OFFSET with dynamic "reference"

[xela05]

Hi guys!

So i'm building a formula where i get values from a dynamic list using this method OFFSET('Husson - Cameleo'!$A$4;0;0;COUNTA('Husson - Cameleo'!$A:$A)-1) where "Husson - Cameleo" is the sheet name where I'm getting the values from.
My question is: can that sheet name be change to a cell ref where I've a drop list with my sheet names?


Thanks!

[ben_hensel]

Yes, use the INDIRECT function in a manner like this:

A1 = dropdown
B1 = OFFSET(INDIRECT("'"&A1&"'!$A$4");0;0;COUNTA(INDIRECT("'"&A1&"'!$A:$A"))-1)

Well, that's untested though.

[TMS]

Probably, using INDIRECT. See this thread: http://www.excelforum.com/excel-form...formation.html


Regards, TMS

[xela05]

Thanks! It's really that! I was close on my tests but hess I has missing a ).....