SUM question Eval?

paulanthony

New Member
Joined
Aug 19, 2005
Messages
9
I want to be able to change sheet wide a formula for a SUM sheet wide at present the formula looks like this..

=SUM(Toome:Dublin!C6)

This is for a summary sheet.
My problem is that C6 occasionally needs to change to say C7. This is replicated in many cells, so I decide to store the value "6" in a cell so that it can change easily across the sheet. As a programmer in other languages I use something like Eval to do this, however im a bit stuck as to how this would work in Excel..

=SUM(Toome:Dublin!C Eval(=CELL(BLAH)(VALUE)) ) kinda gives a feel for what im trying to achieve.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
See if INDIRECT works ( haven't tried it on 3D references recently ) ...
Code:
=SUM("Toome:Dublin!C"&A1)
 
Upvote 0
See if INDIRECT works ( haven't tried it on 3D references recently ) ...
Code:
=SUM("Toome:Dublin!C"&A1)

It don't I'm afraid.

You would need to do something like:

=SUM(N(INDIRECT({"Sheet1","Sheet2","Sheet3"}&"!"&A1)))
 
Upvote 0

Forum statistics

Threads
1,223,429
Messages
6,172,048
Members
452,444
Latest member
ShaImran193

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?

Disable AdBlock

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
Back
Top