Is there an easy way to tranfer data from multiple tabs to a Summary Tab

[pedro-egoli]

I have a Summary sheet , and multiple tabs named like "JUNE 2011".

On Summary tab there is data in 3 rows that comes from the same cell in each of the multiple tabs when that month's data is entered.

At present to transfer this data I enter the equals sign in summary sheet and then select the cell containing data on the "JUNE 2011" type tab.

Is there a way to obtain the data by matching the data in Summary sheet to the Tab name, when the Tab Name is created.
ie. Sumarry sheet "June 2011 - rows 6,23,29" to match with Tab "JUNE 2011-cells H38+I38,E38,G38"

Thanks

Pedro

 

[SydneyGeek]

You can use INDIRECT.
Example...

Column A has sheet names, column B has cell addresses
In C1 put this:
=INDIRECT("'"&A1&"'!"&B1)

Fill down to pull data in from the cells listed in column B.

Denis

 

[pedro-egoli]

Thanks Sydneygeek,
Never had anything to do with INDIRECT and am finding it hard to get my head around it for my spreadsheet which is daily recording of solar production, export to grid and import from grid.
Summary sheet contains the month to date data for individual months and this data comes from other tabs, each of which relate to the month being recorded.

Pedro

 

[SydneyGeek]

Hi Pedro,
INDIRECT lets you build cell references using the *contents* of cells.

So, let's assume that A1 contains First Sheet and B1 contains C9. We want to refer to C9 on First Sheet, and INDIRECT will let us do that. The completed link would be
='First Sheet'!C9
Note the apostrophes because First Sheet has a space in the name.
So, we need
' & [Contents of A1] & '! & [Contents of B1]
...but because we want the apostrophes and exclamation mark to treated as text, they have to go in quotes.
That makes the finished formula
="'" & A1 & "'!" & B1

I've added spaces to make the formula easier to read; the 'real' one doesn't have them.

Denis

 

[pedro-egoli]

Sorry Dennis , a bit too advanced for me, I just can't get my head around your suggestion.

Thanks anyway

Pedro

 

[pedro-egoli]

The following formula was suggested

=INDIRECT("'" & UPPER(TEXT(NOW(), "mmmm yyyy")) & "'!H38", TRUE)+INDIRECT("'" & UPPER(TEXT(NOW(),"mmmm yyyy"))&"'!I38",TRUE)

I tried it in a blank workbook and named sheet 2 "JUNE 2011" and filled cells H38,I38.

On sheet 1 in A1 I placed formula and it returned the total of H38 and I38.
This was pleasing.

Then I went to my spreadsheet and with my "JUNE 2011" sheet having totals in H39 and H39, I recorded formula in Summary sheet .
I thought it would produce the total of cells H38 and H39 however, it returned a #REF error.
Evaluation of error, showed

=INDIRECT(“JUN 2011’H38”,TRUE
was underlined and shown as the cause for #REF error

From Excel help on INDIRECT this message

 If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value

I deleted the INDIRECT and this made
="'" & UPPER(TEXT(NOW(), "mmmm yyyy")) return 'JUNE 2011.

It appears the tab name is the problem though it works on a blank workbook, so what other factors might influence the #REF in my workbook

Pedro