reference same cell on multiple worksheets

[butljody]

I run an excavation company and am recording dump truck weights from a scale in a daily log. In my Excel file I have a worksheet for each day that lists the weight for each load each truck hauled. Each truck has a column on each worksheet so I can add up the totals for each truck each day. I am trying to set up a summary page for each truck so I can analyze the productivity of each truck daily and over time. It takes a lot of time to reference each truck's summary page to each of the daily worksheets to get the information. The daily worksheets are set up identically, so is there a formula I can use that I can drag down and pull the cell reference from each successive day/worksheet rather than basically re-entering the formulas for each day?

[nigelbloomy]

If you keep the total in the same cell you can do this:
On the totals page type =SUM( then click on the first worksheet's tab then hold the Shift key down and click on the last worksheet's tab. Click on B19 where your page totals are, then close parentheses ")".

That will create a sum of all the values in that cell on all of the tabs.

The formula looked like this when I was done:

=SUM('June 13:Sheet25'!B19)

[nigelbloomy]

I think I read your question wrong at first. This formula in C3 will look at the date in B3 and try to find the tab with the name of the date. You can drag this down.

=INDIRECT("'"&TEXT(B3,"mmmm d")&"'!B19")

You would still need to keep the total cell in B19 and keep naming the tabs with the date you want.

[oeldere]

@butljody,

I think you much better of using just 1 worksheet and add all data in that sheet.

If you add the data table-wise, it will be very usefull to make a pivot table.

Pivot tables are a very strong built in function and the options increase on each (new) excel version.

Once you know the tricks, you will be glad you learned it.

[nigelbloomy]

I agree with oeldere. If you can do this in one table, then you can use a pivot table to divide out and sum up anything you want. Just to show how it could be done I attached a file with the first two days put into one table. There is also a pivot table showing how it can add up everything you would want to see.

I know the table looks big, but it is easy to enter new information and not a problem at all for Excel to handle thousands of rows.

[butljody]

Thanks. That makes a huge difference. I learned pivot tables in college, but that was a while ago and I just haven't used them since.