Reference cell location

D

Dan

Want to determine the address of a given data point. The information is in
one excel sheet "data sheet" and the equation will be in another sheet "calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which will be used
for further calculations.

Thanks

Dan
 
P

Peo Sjoblom

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
D

Dan

This works for the basic example but my cell is within another excel sheet.
It does not matter which method I use to locate the data, I need a means of
determining the position within the spreadsheet.

The CELL("address",reference) does not work when I intsert my VLOOKUP or
even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns
an error.

Regards,

Dan
 
P

Peo Sjoblom

It doesn't matter where the the table is, it will return the address with
workbook name and sheet name if it is in another sheet. I suspect that you
implement it incorrectly or that your data does not match (if you get #N/A).
Create a fake table and test and you'll see that it works

=CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1!A2:A7,0)))

will return the cell address in Sheet1

so either you applied it incorrectly or there is no exact matched lookup value


Regards,

Peo Sjoblom
 
D

Dan

I retried the equation and it works for your example but it will not work
when the "reference" for CELL is a VLOOKUP.

I have VLOOKUP working in the cell directly above the CELL equation, then I
copied and inserted the equation into the CELL("address",VLOOKUP(...)) but I
get an error when I select enter.

Not sure where the error is or why it will not work with VLOOKUP.

Regards,

Dan
 
P

Peo Sjoblom

It's because you cannot use VLOOKUP to get this, you have to replace the
vlookup with the index match combination or else it will never work, in my
first example, to quote myself: "It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value"

VLOOKUP looks up a value in the leftmost column and then offset it by the
index number you use, that's the reason,
if you have a VLOOKUP formula in the cell above you still would need index,
assume that the vlookup value is 10 and it was looked up in Sheet1 column
F2:F50

=CELL("address",INDEX(Sheet1!F2:F50,MATCH(VLOOKUP(A1,Sheet1!A2:F50,6,0),Sheet1!F2:F50,0)))

but that doesn't make any sense at all, it's much better to change the
vlookup to an index match



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
D

Dan

My apologies. I missed the statement about that VLOOKUP would not work within
the CELL call. I rearranged my equations to work with INDEX and all other
equations were modified to suit.

I appreciate all the assistance.

Regards,

Dan
 
Top