Function for sheet number

[Ruud Bos]

I've got a question about retrieve the sheet number into a
cell.
I'm currently making a workbook with different sheets.
Each sheet has it's own 'pagenumber' (equals sheet
number). I want to read this out, so when I add a sheet in
the middle, all the number will change automaticly.
Someone got the sollution for me?

Thanks in advance...

 

[Govind]

Hi Ruud,

Try this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

change the cell A1 to the cell where you need the sheet number.

Regards

Govind.

 

[Ruud]

Hey Govind,

I pasted the formula in the Cell A1 and looked what
happened. The cell got filled with the name of the sheet
and not with the number of the sheet. All I want is
that the first worksheet get's the number '1' en the
second '2', etc.

Hope you have a solution for this

Greetings,

Ruud

-----Original Message-----
Hi Ruud,

Try this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) +1,255)

change the cell A1 to the cell where you need the sheet number.

Regards

Govind.

I've got a question about retrieve the sheet number into a
cell.
I'm currently making a workbook with different sheets.
Each sheet has it's own 'pagenumber' (equals sheet
number). I want to read this out, so when I add a sheet in
the middle, all the number will change automaticly.
Someone got the sollution for me?

Thanks in advance...

.

 

[Govind]

Hi Ruud,

Use this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,255)

Regards

Govind.

Hey Govind,

I pasted the formula in the Cell A1 and looked what
happened. The cell got filled with the name of the sheet
and not with the number of the sheet. All I want is
that the first worksheet get's the number '1' en the
second '2', etc.

Hope you have a solution for this

Greetings,

Ruud

-----Original Message-----
Hi Ruud,

Try this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
+1,255)

change the cell A1 to the cell where you need the sheet
number.

Regards

Govind.

I've got a question about retrieve the sheet number

into a

in


.

 

[Govind]

By the way i have assumed that your sheet names will always be sheet
prefixed by the sheet number.like sheet1,sheet2,etc.etc.

Otherwise i might have to suggest a different formula

Regards

Govind

Hi Ruud,

Use this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,255)

Regards

Govind.

Hey Govind,

I pasted the formula in the Cell A1 and looked what happened. The cell
got filled with the name of the sheet
and not with the number of the sheet. All I want is
that the first worksheet get's the number '1' en the
second '2', etc.

Hope you have a solution for this

Greetings,

Ruud

-----Original Message-----
Hi Ruud,

Try this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))

+1,255)

change the cell A1 to the cell where you need the sheet

number.

Regards

Govind.

Ruud Bos wrote:

I've got a question about retrieve the sheet number

into a

cell. I'm currently making a workbook with different sheets. Each
sheet has it's own 'pagenumber' (equals sheet number). I want to
read this out, so when I add a sheet

in

the middle, all the number will change automaticly. Someone got the
sollution for me?

Thanks in advance...


.

 

[Bernie Deitrick]

Ruud,

You could use a user-defined-function:

Function ShtNum() As Integer
ShtNum = Application.Caller.Parent.Index
End Function

Copy the code into a codemodule in the workbook, then use the function like

=ShtNum()

in any cell.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

The code is working fine, thank you.
But when I add a new sheet the page number doesn't change
automatically, I first have to highlight them and push
enter.
Is there a way to let Excel do this, I tried some update
functions but they didnt work properly.

Thanks
Ruud

 

[Guest]

Govind,

I found the solution! With a little bit of VB everything
becomes a bit easier.

tnx for your help

Ruud

-----Original Message-----
By the way i have assumed that your sheet names will always be sheet
prefixed by the sheet number.like sheet1,sheet2,etc.etc.

Otherwise i might have to suggest a different formula

Regards

Govind

Hi Ruud,

Use this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) +6,255)

Regards

Govind.

Hey Govind,

I pasted the formula in the Cell A1 and looked what happened. The cell
got filled with the name of the sheet
and not with the number of the sheet. All I want is
that the first worksheet get's the number '1' en the
second '2', etc.

Hope you have a solution for this

Greetings,

Ruud


-----Original Message-----
Hi Ruud,

Try this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))


+1,255)

change the cell A1 to the cell where you need the sheet


number.

Regards

Govind.

Ruud Bos wrote:

I've got a question about retrieve the sheet number


into a

cell. I'm currently making a workbook with different sheets. Each
sheet has it's own 'pagenumber' (equals sheet number). I want to
read this out, so when I add a sheet


in

the middle, all the number will change automaticly. Someone got the
sollution for me?

Thanks in advance...


.

.

 

[Bernie Deitrick]

Ruud,

It worked for me without any problems.

So, try:

1) Setting your calculation mode to automatic

2) adding the line

Application.Volatile

to the function, as the first line.

3) If those don't work, instead of editing each cell, try a global re-calc
Ctrl-Alt-F9

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

Adding the line into the function worked.
Thanks a lot, it will spare me a lot of time.

Ruud