 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2007INDIRECT AND IF STATEMENT
I have a cell on worksheet A that includes a data validation list in cell c4. The data validation list is a list of named ranges from other worksheets--one range to one sheet. I want to write a formula referencing the range name in cell C4 to go to a cell within that range and if that cell = 0 (it has a formula in it), to return nothing and if it doesn't, to return text. I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell on the worksheet containing the range named in C4. I am getting a #value error. I traced the error and the dependent arrows are pointing to cell I9 on the worksheet I am putting the formula into. What am I doing wrong or is there a better way to accomplish this. Thanks! What's the point of doing this? Why can't you just use =IF(I9=0,"","Special") What do you have in C4?  SignatureRegards, Peo Sjoblom >I have a cell on worksheet A that includes a data validation list in cell >c4. [quoted text clipped - 12 lines] > What > am I doing wrong or is there a better way to accomplish this. Thanks! C4 is a list of named ranges from other worksheets within the workbook. Depending on which range is selected by the user of the spreadsheet, I want it to go to that range, see if the value of I9 is zero and return my yes or no results. > What's the point of doing this? Why can't you just use > [quoted text clipped - 18 lines] > > What > > am I doing wrong or is there a better way to accomplish this. Thanks! Do you have more than one named range in C4. I will assume that you don't because that would be foolish and that you probably have a validation in C4? However I fail to see what it has anything to do with I9 if the name in C4 is a named range, do you always look what's in I9 in all those named ranges if so it would be better if you had the different sheet names in C4. If it is always the ninth cell in the named range that you want to test you could use =IF(INDEX(INDIRECT(C4),9)=0,"","Special") or if you had the sheet names in C4 =IF(INDIRECT("'"&C4&"'!I9")=0,"","Special") SignatureRegards, Peo Sjoblom > C4 is a list of named ranges from other worksheets within the workbook. > Depending on which range is selected by the user of the spreadsheet, I [quoted text clipped - 30 lines] >> > What >> > am I doing wrong or is there a better way to accomplish this. Thanks! yes, I am always looking at I9 in all of those ranges for this formula, but I cannot use the sheetnames, because I am using the names of the ranges for other dependent cell formulas for which I need the names of the ranges. So is there a way to write the formula to reference a specific cell within a range? In looking at your index formula, I would rewrite it to read =(indirect(c4),l9 = 0, "", "special") Does that make sense? But I am still getting an error. > Do you have more than one named range in C4. I will assume that you don't > because that would be foolish and that you probably have a validation in C4? [quoted text clipped - 44 lines] > >> > What > >> > am I doing wrong or is there a better way to accomplish this. Thanks! Are the named ranges of equal size and if they are in what order is I9? If they are the same size starting in the same cell but in different sheets just give us the range and I will give a formula that will work. Meaning that if the named range for instance is always I1:I15 then you can use the INDEX formula I gave you. Or if it is A2: K50 then it could be done as well. There is no way of using it the way you are trying to do but if they are of equal size then yes it is possible SignatureRegards, Peo Sjoblom > yes, I am always looking at I9 in all of those ranges for this formula, > but I [quoted text clipped - 65 lines] >> >> > am I doing wrong or is there a better way to accomplish this. >> >> > Thanks! I assumed that since the indirect reference works with my Vlookup formulas going to those ranges on different sheets that it would work with an IF statement. Yes, my named ranges are all the same size. The size on each sheet is A1:K357 and the cell is always I9, so a formula for that would be appreciated. Thank you for all of your time. Pam > Are the named ranges of equal size and if they are in what order is I9? If > they are the same size starting in the same cell but in different sheets [quoted text clipped - 73 lines] > >> >> > am I doing wrong or is there a better way to accomplish this. > >> >> > Thanks! This should work then =IF(INDEX(INDIRECT(C4),9,9)=0,"","Special") SignatureRegards, Peo Sjoblom >I assumed that since the indirect reference works with my Vlookup formulas > going to those ranges on different sheets that it would work with an IF [quoted text clipped - 97 lines] >> >> >> > am I doing wrong or is there a better way to accomplish this. >> >> >> > Thanks! Peo, that worked perfectly. thank you so much for your help. Pam > This should work then > [quoted text clipped - 101 lines] > >> >> >> > am I doing wrong or is there a better way to accomplish this. > >> >> >> > Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.