reference functions

P

Paul Pedersen

I have a cell that is a pick list of items from a column. Those items are
not values in themselves, but references to cells in various unpredictable
locations on other sheets in the workbook.

Example list column values:

"Value 1" (refers to sheet1!A23)
"Value 2" (refers to sheet2!B7)
"Value 3" (refers to sheet2!D2)
"Value 4" (refers to sheet3!A6)

In this case the pick list would contain the choices Value 1, Value 2, Value
3, Value 4.

When the user selects an item from the list, I need to get a reference to
the cell that value came from, in order to get values from cells in
positions relative to that one. For instance, if the user chooses Value 2, I
might need to return the value of the cell at sheet2!B8.

My thought was to first look for a function that would return the formula in
a specified cell, but I can't even get that far.

Anyone have suggestions?
 
D

DJH

A B
1 Value1 Sheet1!A12
2 Value2 Sheet2!b7
3 Value3 Sheet2!d2
4 Value4 Sheet3!a6


=INDIRECT(VLOOKUP(xxxx,A1:B4,2,FALSE)

WHERE xxxx is your Linked Cell from the drop down list
 
P

Paul Pedersen

I'll try it. Thanks!

DJH said:
A B
1 Value1 Sheet1!A12
2 Value2 Sheet2!b7
3 Value3 Sheet2!d2
4 Value4 Sheet3!a6


=INDIRECT(VLOOKUP(xxxx,A1:B4,2,FALSE)

WHERE xxxx is your Linked Cell from the drop down list
Click to expand...
 
P

Paul Pedersen

Thanks for the attempt, but that does not seem to do what I need. Perhaps I
didn't explain it well. Let me try again.


Sheet 1
A B
1 value1 <formula that returns sheet2!B2, eg>
2
3 = sheet2!a1
4 = sheet2!a2
5 = sheet2!a3
6 = sheet2!a4


Sheet 2
A B
1 Value1
2 Value2
3 Value3
4 Value4


This gives the general idea, but in the actual problem, the cells referred
to in Sheet 1, Column A are not lined up nicely in a column in Sheet 2.
Instead, they can be all over the place, even on different sheets.
 
D

Debra Dalgleish

Why are the cells unpredictable? Do they keep changing? Will Value 1 be
in a different location tomorrow?
 
K

KC Rippstein

Paul, this makes sense to me and should work. In the example below, put the
list somewhere else. Where DJH says A1, put that in AA1, then B1 in AB1.
Now do your normal list:
AA1 =Sheet1!A12
AA2 =Sheet2!B7
AA3 =Sheet2!D2
AA4 =Sheet3!A6
Then in AB, put the actual words that correspond to the cell reference.
So in AB1, just type the word "Sheet1!A12" without the quotes. Repeat that
to AB4. I know it seems like double entry at first, but the end result is
what you want.

When the user is in A1, you have that cell set up with data validation
restricted to the list AA1:AA4. When they choose one of the values
available, then in B1, use the formula DJH gave you.
B1 =INDIRECT(VLOOKUP(A1,AA1:AB4,2,FALSE))
So now the user is in A1 and selects Value 2 from the drop down list.
Immediately cell B1 shows you the word "Sheet2!B7" (without quotes) which I
believe is what you were looking for...the cell address corresponding to the
choice they made. You can then use that value in your future lookup
formulas in columns C, D, etc. by simply using INDIRECT again.
C1 =OFFSET(INDIRECT(B1),0,1)
That example gives you the value in Sheet2!B8, just like you asked for.
 
K

KC Rippstein

Actually, now that I think about it, you can eliminate the double-entry
problem by using indirect in your actual list.
AA1 =OFFSET(INDIRECT(AB1),0,0)
Then drag that formula down to AA4.
 
P

Paul Pedersen

Thanks. That's helpful.

But what I'd really like is to get the text of the formula in a cell. I
don't want to require the user to type that as text.

I need something like this:

If the formula in A1 is =B1, I need a function in C1 that returns "=AB1".

I'm beginning to think that's not possible with Excel. Maybe VBA can get it?
 
K

KC Rippstein

Sorry, Paul, I just don't see how you could get the text equivalent of the
actual formula itself without putting it into text yourself.

I had assumed you were in control of setting up these data validation lists
and could just manually type the text corresponding to the cell
address...the rest is automated from that one entry. If you are asking the
user to set up these data validation lists, then you have to work backwards
and ask the user to type the actual cell address instead of typing = and
then using their mouse to point to the cell they want.

For VBA, put this in a REGULAR code module and then C1 =stringit(a1)

Function stringit(x)
stringit = x.Formula
End Function
..

Happy New Year!
- KC
 
Top