reading sheetnumber?

[Guurt]

Hello All,

Does anyone know what the syntax should be to make a string read a sheet
number from a cel. So, that the sheet number is a variabel, to be read from
another column.

Like, a cel should read the content from cel A1 on Sheet XX, is
"=SheetXX!(A1)"

but now the XX is variabel and the XX is in another cel, so
"=Sheet"XX"!(A1)" ???

What should be the syntax of the XX part?

I seem not to get it right...

Thanx,
Guurt

 

[Roman]

Hi Guurt,
unfortunately for you there is not such a function.
Sheetxx is a name of sheet that can be changed to anything (eg.
"myfirstsheet", "mydadyssecondsheet") and then there are no numbers in
the name such you could read.
You can get an index of sheet - that means its sequense in workbook by
using a macro or user defined function such as:

Function thissheetorder() As Integer
thissheetorder = ActiveSheet.Index
End Function

Put it into a module of your workbook or your personal macro workbook
and you will find it between userdefined functions.

 

[Roman]

Hi Guurt again,
I might not understand you well. Then this could be a solution for you:

=INDIRECT(ADDRESS(1;1;1;1;"sheet"&B2))

where in B2 is number of sheet you want to read.

Reads A1 on sheetXX

 

[Guurt]

Thanx Roman!

Your formula did the trick!!

(man, did I spent a few hours on syntaxing before I got your tip...)

Thanks again!

Guurt

 

[Roman]

Man, I'm glad it helped!