Offset() problems

[Utterly Bamboozled]

Hi guys,

Been using offset to create dynamic range names for inclusion in validation.

Having an issue with the following formula:

OFFSET(cell ref,,columns,height) which I've used as a name, but then in the drop down list, it returns both the offset cell reference and the values I'm after.

I.e. for an offset cell ref of 'A' and my desired values in another column being 1,2,3 my list is returning A1, A2, A3 instead of 1,2,3.

Hope this makes sense, would be eternally grateful for some advice!

Cheers

 

[Aladin Akyurek]

Hi guys,

Been using offset to create dynamic range names for inclusion in validation.

Having an issue with the following formula:

OFFSET(cell ref,,columns,height) which I've used as a name, but then in the drop down list, it returns both the offset cell reference and the values I'm after.

I.e. for an offset cell ref of 'A' and my desired values in another column being 1,2,3 my list is returning A1, A2, A3 instead of 1,2,3.

Hope this makes sense, would be eternally grateful for some advice!

Cheers

Care to specify the current range you want to make dynamic and the sheet including that range? Does the range house numbers or text or both?

 

[Utterly Bamboozled]

Sure, dynamic range is text as is the contents of the offset() cell ref.

Below is the formula if it helps:

OFFSET(INDIRECT("'Client Database'!"&ADDRESS(MATCH(COC!$E$13,'Client Database'!$B:$B,0),COLUMN('Client Database'!$B$9))),,COLUMNS('Client Database'!$B$9:$E$9),VLOOKUP(COC!$E$13,$H:$I,COLUMNS($H$3:$I$3),0))

Thanks for the help!

 

[Utterly Bamboozled]

Worked it out - it's because I had was was using 1 too many columns in the offset() function. Forgot it doesn't count the column where the formula is unlike vlookup().

Thanks for the help regardless!

 

[Aladin Akyurek]

Sure, dynamic range is text as is the contents of the offset() cell ref.

Below is the formula if it helps:

OFFSET(INDIRECT("'Client Database'!"&ADDRESS(MATCH(COC!$E$13,'Client Database'!$B:$B,0),COLUMN('Client Database'!$B$9))),,COLUMNS('Client Database'!$B$9:$E$9),VLOOKUP(COC!$E$13,$H:$I,COLUMNS($H$3:$I$3),0))

Thanks for the help!

Care to specify the current range this formula is expected to refer to?