Help with Indirect Function and Multiple Worksheets

[EliseFreedman]

Hi there

I have individual worksheets for each week of the year (the spreadsheet wasnt designed by me). Each 4 week period is then summarised in what the worksheet creator has called Period Rollup. The individual worksheets are called FW 1 Data, FW 2 Data, FW 3 Data. Each worksheet has the same layout. In the period rollup worksheet i want to create a formula that adds the formulas for each of the 4 weeks. I can create these formulas easily enough but then would have to go through the same process for each separate 4 week period. This morning someone on here suggested reading up on the INDIRECT function for referencing the different worksheets. I am now working on that. I have created another worksheet called Sheets with a table in it as below
FW#
FW 1
2
3
4
5
6
7
8
In the Indirect Formula I am trying to refer to the different sheets eg.

=INDIRECT(Sheets!A2&Sheets!B2&" data"&"!B3")

Where sheets!A2 contains the string "FW" and Sheets!B2 contains the sheet number. The cell B3 stays the same in each worksheet. It is just the name of the worksheet that changes. When I try the above formula I just get #ref. Im not sure what is wrong with the formula and also cant really see how this is going to solve my problem because obviously I am still going to have to go through the formulas and change the formula from B2 to B3 to B4 etc

Can anyone help

 

[GlennUK]

You have to get the string exactly right for the reference to resolve correctly. I can an error straight away ... it looks like your concatenation would result in FW2 data!B3 whereas you said that the sheets were named FW 2 data ... see the difference? As for the B2 to B3 to B4 changes, you can generate the string for reference in many ways, such as "B"&ROW().

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Salut39]

You can use this user defined function to list your sheet name (given kindly to me by Zack)

Public Function Sheet(wsIndex As Long) As String
Dim wb As Workbook
On Error Resume Next
Application.Volatile True
Set wb = Workbooks(Application.Caller.Parent.Parent.Name)
Sheet = "ERROR!"
Sheet = wb.Worksheets(wsIndex).Name
End Function

Copy this to a new module ALT + F11
In the cell =SHEET(ROW(A1) and then use you INDIRECT function.

 

[xlhelp]

Elise,

I find it very interesting that none of the replies that you receive even get an acknowledgement, let alone the fact that some of them may even actually deserve stars.

Please forgive my intrusion.

Member- AAAA Association Against Acronym Abusers