Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Data From Different Sheets Via Formula/ae | Excel Discussion (Misc queries) | |||
IF formula over multiple sheets | Excel Discussion (Misc queries) | |||
formula to add up on multiple sheets | Excel Worksheet Functions | |||
Help! Formula between the sheets | Excel Worksheet Functions | |||
Referencing Sheets in a Formula | Excel Discussion (Misc queries) |