Worksheet defined cell sums

flextera · #1 06-17-2014, 07:05 AM

I have a summary sheet.

I have multiple sheets with info.

I want the user to be able to type in 2 worksheets in the summary sheet.
For example: Sheet1-Sheet7 and get the SUM of a certain cell between those two sheets they type.

I have attached exactly what I am looking for.

whatsup · #2 06-17-2014, 08:52 AM

Hi

You're quite close though cells A2:A4 in your example are empty which leaves you to a REf-error.
So the first thing to remember, if the range you refer to contains a reference which doesn't exist you will end up with the error!
Therefore just fill the names of the desired sheets, e.g.:
A2: Sheet1
A3: Sheet2
Change your formula to this range and it will do the trick.

BobBridges · #3 06-17-2014, 08:54 AM

The syntax if you already knew the sheet names would be like this:

Code:

=SheetA!H2+SheetB!H2

Since you don't know the sheet names to plug in, you have to get them from C2 and C4, where the user put in the sheet names, and you have to construct a reference using the INDIRECT function, like this:

Code:

=INDIRECT(C2&"!H2")+INDIRECT(C4&"!H2")

The INDIRECT function takes a character string and interprets it as a cell reference, you see. Does this make sense to you?

flextera · #4 06-17-2014, 11:12 AM

Thanks for the help you two!!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
getting the worksheet name in a cell	derohanes	Excel	9	02-14-2013 01:29 PM
Help with SUMs in tables	mhurford	Word Tables	1	09-02-2012 03:06 AM
Range.Information(wdStartOfRangeRowNumber): Application-defined or Object-defined err	tinfanide	Excel Programming	2	06-09-2012 10:19 AM
Cell formating on Protected worksheet	AHB	Excel Programming	2	02-27-2012 10:25 AM
click a cell to go to a worksheet	victor	Excel	0	10-27-2006 02:57 PM