# Summarize identical sheets (invoices) into ONE

#### marvinbower

##### New Member
I know that sounds simple and stupid

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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Assuming that you have 4 sheets, Sheet1, Sheet2, Sheet3 and Sheet4 (ordered in that way), you can put the following on Sheet1:

Code:
``=SUM(Sheet2:Sheet4!A1)``

which will sum A1 in sheet2, 3 and 4.

Cheers!

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

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:
Code:
``="=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:
Code:
``````Sub ChangeRefs()
Dim cell As Range
For Each cell In Selection

cell.Formula = cell.Value

Next cell
End Sub``````

Replies
12
Views
562
Replies
5
Views
464
Replies
5
Views
183
Replies
3
Views
514
Replies
2
Views
398

1,203,396
Messages
6,055,163
Members
444,767
Latest member
bryandaniel5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back