Tags: basic, details, excel, formula, microsoft, msdn, named, perhaps, queries, sheets, similar, software, various

Formula from various sheets

On Microsoft » Microsoft Excel

7,378 words with 4 Comments; publish: Thu, 22 May 2008 07:11:00 GMT; (306101.56, « »)

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.

All Comments

Leave a comment...

  • 4 Comments
    • 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.

      #1; Thu, 22 May 2008 07:12:00 GMT
    • 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.

      #2; Thu, 22 May 2008 07:13:00 GMT
    • 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.

      #3; Thu, 22 May 2008 07:14:00 GMT
    • 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" <Ali.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:4B20BB43-98F7-4501-B7C9-511EF7DEBE0B.excel.todaysummary.com.microsoft.com...

      > 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.

      #4; Thu, 22 May 2008 07:16:00 GMT