Formula from various sheets
 

Hi, I have seen similar queries but still can't seem to get it correct as
perhaps i need more basic details. i have 20 sheets. each named according
to a staff name: Peter, john, david etc
I then have a summary sheet.
i would like column A1 to read the staff name on the worksheet tab (is this
possible?)
Secondly, Column B needs to read overtime for staff. Appears on cell H10 on
EACH sheet. So B1: would show overtime for peter. B2, overtime for David etc
Column C would then read another cell from each of the staff sheets, so that
i would have 1 x summarised sheet, reading all the values from their
individual sheets. Please can you assist. Thanks, and apologies if i have
asked a few individual for help, as i replied to previous postings.

Formula from various sheets
 

This is how you can get the name of the current worksheet

You have to make use of CELL, MID, LEN worksheet functions

Type thie below formula in the desired cell where you want the current
worksheet's name to be displayed-

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

Regards,
Pranav Vaidya

"Ali" wrote:

Hi, I have seen similar queries but still can't seem to get it correct as
perhaps i need more basic details. i have 20 sheets. each named according
to a staff name: Peter, john, david etc
I then have a summary sheet.
i would like column A1 to read the staff name on the worksheet tab (is this
possible?)
Secondly, Column B needs to read overtime for staff. Appears on cell H10 on
EACH sheet. So B1: would show overtime for peter. B2, overtime for David etc
Column C would then read another cell from each of the staff sheets, so that
i would have 1 x summarised sheet, reading all the values from their
individual sheets. Please can you assist. Thanks, and apologies if i have
asked a few individual for help, as i replied to previous postings.

Formula from various sheets
 

Ali,

This assumes you first worksheet is the summary sheet. On workbook open it
reads all the sheet tabs and overtime worked into the first sheet. It doesn't
have to be workbook open code it could just as easily be a module called with
a button.

Private Sub Workbook_Open()
x = 1
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select
If x = 1 Then GoTo 100
otworked = Range("H10").Value
Worksheets("Sheet1").Cells(x, 1).Value = wSheet.Name
Worksheets("Sheet1").Cells(x, 2).Value = otworked
100
x = x + 1
Next wSheet
End Sub

Mike

"Ali" wrote:

Hi, I have seen similar queries but still can't seem to get it correct as
perhaps i need more basic details. i have 20 sheets. each named according
to a staff name: Peter, john, david etc
I then have a summary sheet.
i would like column A1 to read the staff name on the worksheet tab (is this
possible?)
Secondly, Column B needs to read overtime for staff. Appears on cell H10 on
EACH sheet. So B1: would show overtime for peter. B2, overtime for David etc
Column C would then read another cell from each of the staff sheets, so that
i would have 1 x summarised sheet, reading all the values from their
individual sheets. Please can you assist. Thanks, and apologies if i have
asked a few individual for help, as i replied to previous postings.

Formula from various sheets
 

Hi Pranav, thanks i am able to get the name of the CURRENT sheet, on the
master copy. However, I was hoping that there was some way, to have column
A1-20 9on the master sheet) to read the names of ALL the 20 worksheets. Is
this possible?

"Pranav Vaidya" wrote:

This is how you can get the name of the current worksheet

You have to make use of CELL, MID, LEN worksheet functions

Type thie below formula in the desired cell where you want the current
worksheet's name to be displayed-

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

Regards,
Pranav Vaidya

"Ali" wrote:

Hi, I have seen similar queries but still can't seem to get it correct as
perhaps i need more basic details. i have 20 sheets. each named according
to a staff name: Peter, john, david etc
I then have a summary sheet.
i would like column A1 to read the staff name on the worksheet tab (is this
possible?)
Secondly, Column B needs to read overtime for staff. Appears on cell H10 on
EACH sheet. So B1: would show overtime for peter. B2, overtime for David etc
Column C would then read another cell from each of the staff sheets, so that
i would have 1 x summarised sheet, reading all the values from their
individual sheets. Please can you assist. Thanks, and apologies if i have
asked a few individual for help, as i replied to previous postings.

Formula from various sheets
 

The problem is that you need to know the name of the sheet, to be able to
link to it and get the name of that sheet. It would then reflect changes but
that is all AFAICS.
--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ali" wrote in message
...
Hi Pranav, thanks i am able to get the name of the CURRENT sheet, on the
master copy. However, I was hoping that there was some way, to have
column
A1-20 9on the master sheet) to read the names of ALL the 20 worksheets.
Is
this possible?

"Pranav Vaidya" wrote:

This is how you can get the name of the current worksheet

You have to make use of CELL, MID, LEN worksheet functions

Type thie below formula in the desired cell where you want the current
worksheet's name to be displayed-

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

Regards,
Pranav Vaidya

"Ali" wrote:

Hi, I have seen similar queries but still can't seem to get it correct
as
perhaps i need more basic details. i have 20 sheets. each named
according
to a staff name: Peter, john, david etc
I then have a summary sheet.
i would like column A1 to read the staff name on the worksheet tab (is
this
possible?)
Secondly, Column B needs to read overtime for staff. Appears on cell
H10 on
EACH sheet. So B1: would show overtime for peter. B2, overtime for
David etc
Column C would then read another cell from each of the staff sheets, so
that
i would have 1 x summarised sheet, reading all the values from their
individual sheets. Please can you assist. Thanks, and apologies if i
have
asked a few individual for help, as i replied to previous postings.