Summarize identical sheets (invoices) into ONE

[marvinbower]

I know that sounds simple and stupid

but ....ray: please help

I've an Excel file with multiple identical (except values) sheets. Each one is an invoice.
So the format is identical, values are different.

How do I create a sum up of all the invoices
A sheet where there is a column that copies automatically all the dates, prices, totals etc of all different sheets

I can do it "manually", writing inside ="sheetname"!$x$y
but it doesnt automate

I hope I've been clear and you can help

Marvin

 

[JLeno]

Assuming that you have 4 sheets, Sheet1, Sheet2, Sheet3 and Sheet4 (ordered in that way), you can put the following on Sheet1:

=SUM(Sheet2:Sheet4!A1)

which will sum A1 in sheet2, 3 and 4.

Cheers!

 

[marvinbower]

Sorry

I don't have to SUM

I want to list the values in a column
so that for exp the first column will have following values
COLUMN A
sheet1!$H$1
sheet2!$H$1
...
sheetN!$H$1

the question is, do I have to manually write "sheetX!$H$1", because if I copy/past down it doesn't past correctly

 

[JLeno]

There is no built-in Excel functionality to do this (to my knowledge)... But of course there are work-arounds

The work-arounds are only interesting if you have a lot of sheets, but you probably do. You could enter the following formula:

="=Sheet" & ROW(A1) & "!$H$1"

This formula will now display =Sheet1!$H$1

And now you can either:
1. Copy selection and paste as values, then for each cell press F2 and then Enter
2. Use the following macro to change the long formulas to the good formulas:

Sub ChangeRefs()
Dim cell As Range
For Each cell In Selection
    
    cell.Formula = cell.Value
    
Next cell
End Sub