using same dynamic range over several worksheet

[VNV]

HI all
I have a workbook which has several worksheets, each worksheet has several columns of text, the number of entries in each columns is dynamic.

I am using a combobox, to navigate to the top of each column, and would like to update another combobox with the values from one of the columns, so that when a user makes a selection, they will be taken to the cell containing this value and be able to read the relevant information in the cell next to it.

The columns from which the range is selected is the same in each worksheet (D, G, etc..), and I was hoping to use the same piece of code, the offset function and name each ranges the same on each worksheet and just place the relevant code into the case statement which moves the combobox to the relevant column head.

=OFFSET(SheetName!$E$23,0,0, COUNTA(SheetName!$E$23:$E$200),1)

Finally here's the problem how to I go about replacing SheetName!, with the active sheet name (please tell me it's that simple) and assign that to the range.


Sorry can't post an example of the workbook,

Thanks in advance

 

[mikerickson]

Perhaps this

=OFFSET(!$E$23,0,0, COUNTA(!$E$23:$E$200),1)

With names, omitting the sheet name (but leaving the !) makes the formula relative to the sheet
!$A$1 means "cell A1 of the sheet that the name is use on"

 

[pgc01]

Hi

I think Mike's solution will solve your problem.

I don't usually use the "!" alone to default to the active sheet because I remember having problems with the refreshing of the names in older versions of Excel. I haven't experienced that refreshing problem in the last excel versions.

Another option, that I never had problems with, is to use Indirect():

=OFFSET(INDIRECT("E23"),0,0, COUNTA(INDIRECT("E23:E200")),1)