Copy Value of cell to call sheet name

**rbpd5015** · 08-03-2011, 01:46 AM

Calling Worksheet name from Cell name

ok guys I got this formula

=SUM('Session 1'!$D$2:$D$9999)

On my summary page

however I need to be able to change the "Session 1" with my adjacent cell

for example my summary page has

Session 1 and here is where I got that formula returning my profit
Session 2
and so on

I tried to drag it down, but I couldn't get the 1 to go to 2 and 3 and 4 etc?

Also part #2 of the question I put 9999 since I do not think my sheet will ever get that long. is their an easier formula to add up as many rows that have data?

Thanks
Matt

**Azam Ali** · 08-03-2011, 02:20 AM

suppose the sheet name:

Session 1 is in cell A2
Session 2 is in cell A3 and so on

try it

=SUM(INDIRECT("'"&A2&"'!$D$2:$D$9999")) and copy down

if you dont want to mention 9999 then use

=SUM(INDIRECT("'"&A2&"'!$D$2"):INDEX(INDIRECT("'"&A2&"'!D:D"),MATCH(9.99999999999999E+307,INDIRECT("'"&A2&"'!D:D")))) and copy down

If your problem is not solved, upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

**rbpd5015** · 08-03-2011, 02:53 AM

Ali

One more question

now I want to add

the name of my workbook is is F2 and I know the extension is always Xlsx

to

=SUM(INDIRECT("'"&A2&"'!$D$2:$D$9999"))