WorkSheet number

[pwbrown]

Hello,

Is there any way to reference a worksheet number in a cell formula in a different worksheet without having to know the worksheet name.

Eg. I currently have: =Global!B1
This gets the value from the cell B1 in the global worksheet, but I want to be able to doing something like = worksheet(3)!B1

Kind regards,

Peter

 

[Brianwarnock]

Worksheets can be referenced by an index number

Worksheets(1). Etc

But note that the index number can be changed by additions and deletions,the number is the sheet number counting from the left.

Brian

 

[pwbrown]

Worksheets can be referenced by an index number

Worksheets(1). Etc

But note that the index number can be changed by additions and deletions,the number is the sheet number counting from the left.

Brian

So how can I use this to return the value in B1 in a cell formula?

 

[Brianwarnock]

Ah, just realised that you want to do this in the worksheet not VBA, sorry I don't know, I suspect that it cannot be done, cell formulae are not normally dynamic so why is it needed.

Brian

 

[pwbrown]

Ah, just realised that you want to do this in the worksheet not VBA, sorry I don't know, I suspect that it cannot be done, cell formulae are not normally dynamic so why is it needed.

Brian

That's what I thought from research but I was hoping there might have been a way.
I was creating a template to gather information from 6 worksheets but one worksheet can have two different names.

 

[lemo]

it may or may not help your cause - there is a neat wildcard trick to collect info from ALL the worksheets EXCEPT the one you are on, e.g -
=SUM('*'!A2)
will sum cells A2 of all the worksheets, except the one where the formula is.
l

 

[pwbrown]

it may or may not help your cause - there is a neat wildcard trick to collect info from ALL the worksheets EXCEPT the one you are on, e.g -
=SUM('*'!A2)
will sum cells A2 of all the worksheets, except the one where the formula is.
l

Thanks for your reply, but unfortunately it doesn't help my cause and I actually knew that already.

I came up with a solution to just create a macro which will find / replace the name if it's the wrong one for the template.

 

[Brianwarnock]

Can you use vba with your template?

The attached shows two simple functions, fgetval siply gets a value from a sheet referenced by index, the other fgetname uses the index to get the sheetname and the INDIRECT function to use it.

sheet2 shows the use of these to obtain the value of sheet values!B1 and also the use of fgetname in VLOOKUP to get data from sheet3.
I don't know if this is of any help.

Brian

 

[pwbrown]

Can you use vba with your template?

The attached shows two simple functions, fgetval siply gets a value from a sheet referenced by index, the other fgetname uses the index to get the sheetname and the INDIRECT function to use it.

sheet2 shows the use of these to obtain the value of sheet values!B1 and also the use of fgetname in VLOOKUP to get data from sheet3.
I don't know if this is of any help.

Brian

That would work but not with what I have to do, I have hundreds of workbooks and it would be a nuisance to have to put that in each of them.
Thanks anyway, I'm sure it will help someone else here.