Referencing cells to specify sheet range

0
votes

My workbook contains a summary sheet which summarises the data held in the other sheets. My current formula in the summary sheet in cell C6 adds up cell B7 in all of the other sheets, RKW3455 being the first sheet to include in the calculation and Batch_20 bring the last sheet to include.

=SUM('RKW3455:Batch_20'!B7)

It works fine but the tab names will change and so to avoid having to change the sheet name within the formula I wanted to put the first sheet name into J2 and the last sheet name into J3. Then I wanted to reference those cells in the formula which will just make it easier to amend when there are new sheets. I can't get it to work though. This is what I have:

=SUM(INDIRECT("'"&J2):INDIRECT(J3&"'"&"!")B7) I've also tried =SUM(INDIRECT("'"&J2):INDIRECT(J3&"'")!B7)

Can anyone correct where I'm going wrong please?

Thanks

1
excelexcel-formula
INDIRECT does not work in 3-D, i.e. you cannot use it to specify the first and last worksheet. You can make a list of worksheets and use indirect to sum through those or create and hide two static dummy worksheets to use as start and stop. Alternately, a udf may be better. – user4039065
When using 3D formula I usually add two blank sheets called Start and End. The formula will always be =SUM('Start:End'!B7) as long as all the other sheets are between these two. Edit.... and just realised that's what @Jeeped said. – Darren Bartrup-Cook
Thanks both, no wonder I couldn't get it to work then! Great idea with the dummy sheets, I'll do that :) – AIMEE

1 Answers

0
votes

Used a workaround as suggested by Jeeped and Darren Bartrup-Cook in the comments. Created a 'start' sheet and an 'end' sheet to sum the sheets in between.