How to insert a user-selected value into an INDIRECT(ADDRESS formula?!?!
Posted by Dustin on March 21, 2001 1:06 PM
Good afternon all,
How should I go about writing a formula to insert a user-selected value (chosen from a drop-down list) into a formula to look up a value in a different workbook?
The value to be selected is the name of a particular office (which also corresponds to the name of the tabs in the source workbook) for our company.
I have a working version of the formula provided I actually insert the office name into the formula, but in order to save tons of time, I need to determine how to use a cell reference to use whatever name is selected by the user into the formula so that I can return the value from the correct worksheet in the source workbook.
Thanks in advance.
Posted by Mark on March 21, 2001 2:10 PM
I could be wrong (and probably am), but I don't think you can use INDIRECT to access a different workbook other than the active one.
Posted by Mark on March 21, 2001 2:14 PM
It might be easier to do a search and replace of your existing formula with the selection from the drop-down list
Posted by Aladin Akyurek on March 21, 2001 2:21 PM
Enter what follows on Sheet1:
A1 =Sheet1!$a$2
A2 1
Entrer on Sheet2:
=INDIRECT(Sheet1!$A$1)
Aladin
Posted by Aladin Akyurek on March 21, 2001 2:25 PM
Dustin
Just curious: what is the formula, its location, and which location must be referenced via a user-selected value?
Aladin
Posted by Mark on March 21, 2001 2:30 PM
Yes, you can access another WORKSHEET in the active workbook, but not another WORKBOOK. (I think)
Mark
Posted by Dustin on March 21, 2001 2:38 PM
Re: How to...Yes, Mark, I do need to access another workbook, not worksheet
Aladin,
The formula that I have now reads like this:
=IF(TODAY()<E$4,"",INDIRECT(ADDRESS(6,7,4,TRUE,"[IBMActivity]IL_OB")))
The workbook that it is accessing is IBMActivity, the worksheet within IBMActivity that I need to access is the variable that would be determined by the user. In the case above I have manually entered IL_OB to make the formula actually work.
The cell where the user makes the choice of Office Location (IL_OB in my example) is $B$1 on the destination page. The cell where the value is located that I need to lookup is G7 on whatever sheet (sheet names match the office names in the drop-down list) name is selected by the user.
I need to figure out a way to write the formula such that as the office name chosen by the user changes, the formulas will update.
Hope this helps clarify. Please send back any questions. Thanks again.
Posted by Aladin Akyurek on March 21, 2001 2:39 PM
Mark
Enter on Sheet1 in Junk.xls:
A1 =[Junk.xls]Sheet1!$a$2
A2 1
Enter on Sheet1 in Junk2.xls
A1 =INDIRECT([Junk.xls]Sheet1!$A$1)
Appears OK too.
Aladin
Posted by Dustin on March 21, 2001 2:40 PM
Re: followup - Sorry the formula in my last post got screwed up some how
Posted by Aladin Akyurek on March 21, 2001 3:01 PM
Re: followup - Sorry the formula in my last post got screwed up some how
Dustin
I'm not sure I got the situation right, but if your formala and $B$1 are on the same sheet, and IL_OB is one the user-selectible value, you could use:
Enter in some cell, say A1, on the same sheet
A1 =[IBMActivity]
and modify your formula as follows:
=INDIRECT(ADDRESS(6,7,4,TRUE,CHAR(34)&A1&$B$1&CHAR(34))))
Does it work?
Aladin
Posted by Dustin on March 21, 2001 5:34 PM
Re: Thanks Aladin! I'll try it.
Aladin,
Thanks for your response. I will need to try this at work in the morning. I will post a new thread if I have any problems.
Thanks again for all of your help.
Dustin
