JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
I have a sheet Settings, A1 contains value "Check_Values", A2:A? are values to be allowed in a drop-down data validation list, where ? is unknown row number.
For a dynamic range, I have named A1 ("Check_Values") and then use formula to name range ("Check_Values_List") for dynamic list: =OFFSET(Check_Values,1,0,COUNTA(Settings!$A:$A)-1,1)
I'd like argument in COUNTA to not refer to sheet name and column address; instead refer to "The column named range Check_Values is in" so there are no direct references to sheet name and column header in the formula.
I.e. =OFFSET(Check_Values,1,0,COUNTA(column Check_Values is in)-1,1)
Can someone suggest correction? Thanks in advance,
Jack
I have a sheet Settings, A1 contains value "Check_Values", A2:A? are values to be allowed in a drop-down data validation list, where ? is unknown row number.
For a dynamic range, I have named A1 ("Check_Values") and then use formula to name range ("Check_Values_List") for dynamic list: =OFFSET(Check_Values,1,0,COUNTA(Settings!$A:$A)-1,1)
I'd like argument in COUNTA to not refer to sheet name and column address; instead refer to "The column named range Check_Values is in" so there are no direct references to sheet name and column header in the formula.
I.e. =OFFSET(Check_Values,1,0,COUNTA(column Check_Values is in)-1,1)
Can someone suggest correction? Thanks in advance,
Jack
Last edited: