Help on Formula - Multiple Worksheets

Hi,

there are several ways to approach this. If your sheets are called Sheet1, Sheet2, Sheet3, etc, then you could use something like this. If your formula is in, say, row 5

=INDIRECT("Sheet"&ROW()-4&"!A47")

you can copy this down and it will put the next sheet's A47 value in the next row.

Or, if your sheets have different names, you could create a list of sheet names in column A and then use something like

=INDIRECT(A1&"!A47")

so the formula evaluates to whatever text is in A1 and adds the cell reference. If A1 has the text MySheet, then it would be

=MySheet!A47

does that help?

OK. You have a looong list of complicated sheet names that are not easy to iterate. It also will not be fun to manually type such a list of sheet names, so that a formula can reference them. Therefore, here's the plan:

On the Trade Summary sheet, you'll insert a new column before column A,
then use a nifty Excel trick to populate that column with all the sheet names in your workbook,
then use an INDIRECT formula to populate the first row of your summary tab,
then copy these formulas down to populate the rest of the list.

Ready?

Here goes.

First, the nifty trick. We'll use an old Excel function that still works with 2003 and 2007 but stems from the dark ages of Excel before VBA. Bear with me.

- Click Insert - Names - Define to set up a new range name
- enter WBSheets in the Name box and the following formula in the Refers to box:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Click OK to save the range name.

Now, insert a column before column A. We'll hide this column later, so don't worry if it mucks up your spreadsheet design.

In the new column A, go to row 16, which is the header row for your summary sheet.
Enter this formula in A16:

=INDEX(WBSheets,ROWS($A$16:$A16))

If your real life sheet looks different from the one you posted, and the header for the summary is on a different row, adjust the ranges above, but make sure that the $ signs stay as they are!!!

Now copy this formula down and see your list of sheet names appear.

In B17, your first data row for the summary, enter

=INDIRECT("'"&$A17&"'!A47")

Caution, there are some double " and single ' quotes next to each other, so you want to make sure to get this right. Maybe you want to copy the formula above and adjust the cell addresses.

In C17 enter

=(INDIRECT("'"&$A17&"'!B47"))

Make sure your columns B and C are formatted with "General" if the results look funny at first.

Copy the formula into the cells of the first summary row and adjust the cell reference after the ! sign to suit.

Now, copy the formulas down your table.

Viola! all your details in place.

Finally, hide column A so you don't have to look at the list of sheet names.

If you ever change the name of a sheet, or insert a new sheet, Excel will update everything automatically, you just need to ensure that you have as many rows of formulae as sheets in the workbook.

Attached workbook shows in red where I have put in the formulas described in this solution.

cheers

afterthought:

It is actually possible to skip the hidden helper column A, and instead do

=INDIRECT("'"&INDEX(WBSheets,ROWS($A$16:$A17))&"'!A47")

in one step, although the formula may look a bit intimidating to the untrained eye.