braindiesel
Well-known Member
- Joined
- Mar 16, 2009
- Messages
- 571
- Office Version
- 365
- 2019
- 2010
- Platform
- Windows
I have a named range called "findIt" that i use in a validation list referencing the following formula
=OFFSET(myLists!$B$1,MATCH(myLists!C12,myLists!$A:$A,0)-2,0,COUNTIF(myLists!$A:$A,myLists!C12)-1,1)
which works perfectly at reference the cell to the left (C12) when I use this for a dropdown list in D12 on the myLists sheet
BUT
I don't want it limited to the myLists Sheet
I can create one of these for each relevant tab, but I would rather just have one work if possible... my ultimate goal is this...
=OFFSET(myLists!$B$1,MATCH(C12,myLists!$A:$A,0)-2,0,COUNTIF(myLists!$A:$A,C12)-1,1)
instead of this...
=OFFSET(myLists!$B$1,MATCH(myLists!C12,myLists!$A:$A,0)-2,0,COUNTIF(myLists!$A:$A,myLists!C12)-1,1)
Anyone know a trick to be a truly relative cell reference, regardless of which sheet?
=OFFSET(myLists!$B$1,MATCH(myLists!C12,myLists!$A:$A,0)-2,0,COUNTIF(myLists!$A:$A,myLists!C12)-1,1)
which works perfectly at reference the cell to the left (C12) when I use this for a dropdown list in D12 on the myLists sheet
BUT
I don't want it limited to the myLists Sheet
I can create one of these for each relevant tab, but I would rather just have one work if possible... my ultimate goal is this...
=OFFSET(myLists!$B$1,MATCH(C12,myLists!$A:$A,0)-2,0,COUNTIF(myLists!$A:$A,C12)-1,1)
instead of this...
=OFFSET(myLists!$B$1,MATCH(myLists!C12,myLists!$A:$A,0)-2,0,COUNTIF(myLists!$A:$A,myLists!C12)-1,1)
Anyone know a trick to be a truly relative cell reference, regardless of which sheet?