Offset.

[Guest]

If let's say My A1 cell formula is =sheet2!a2 on my sheet1, and this pulls up
a client's name, and i want to have a formula in a3 of Sheet1, that will pull
up the data on =sheet2!b2..


BUT...
i want i want the formula to read offset(a1,0,3) Only thing, the offset
doesnt read the actual formula that is in A1. How do i get the offset to read
the formula in A1.
=sheet2!b2,

I want it to work so i have a formula set up in the client's number section,
that all i have to do is type in the reference in my A column of my Sheet2,
in my sheet1 and the client numbers will auto populate..

Is this possible.. it's so hard to explain this proprely i think.
Thanks for your help

 

[Guest]

In Sheet1,

Try in A3:
=IF(A1="","",INDEX(OFFSET(Sheet2!$1:$1,MATCH(A1,Sheet2!A:A,0)-1,),2))

 

[Guest]

In case you are copying A3 across to return similarly
we would need to fix the match col in Sheet2,
ie use: Sheet2!$A:$A

In A3 would be:
=IF(A1="","",INDEX(OFFSET(Sheet2!$1:$1,MATCH(A1,Sheet2!$A:$A,0)-1,),2))
A3 can now be copied across

---