# Sheet Reference from Cell

1. ## Sheet Reference from Cell

Hi All I hope you can help.

Attached is a screenshot which I hope will explain it.

The formula I have is =SUM('1:31'!D24)

What I need to be able to do is to have the sheet names come from entries in cells.

ie. =SUM('B20:D20'!D24)

So if someone types 3 in B20 and 10 in D20 it will sum cells D24 from sheets 3 to 10.

How can I do this?

2. Hi,

This seems to work

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(B20&":"&D20))&"'!\$D\$24"),">0",INDIRECT("'Sheet"&ROW(INDIRECT(B20&":"&D20))&"'!\$D\$24")))

VBA Noob

3. When I put in
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(B 20&":"&D20))&"'!\$D\$24"),">0",INDIRECT("'Sheet"&ROW (INDIRECT(B20&":"&D20))&"'!\$D\$24")))

It gives me an error. #REF!

4. See if removing the space helps between B and 20

VBA Noob

5. Tried that, It didn't work.

I've attached the speadsheet. Sheets 1 and 2 have enough data in them so it should be working.

6. Thanks for all the help, someone found it for me.

=SUM(N(INDIRECT(ROW(INDIRECT(B20&":"&D20))&"!D24")))

with CONTROL+SHIFT+ENTER and it works

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1