xoScarecrowox
New Member
- Joined
- Apr 2, 2019
- Messages
- 15
Hi all, many attempts at this one but unable to solve. So I have workbook with a varying number of worksheets which are listed in column A, column B has a Yes or No option pulldown, what I am attempting to do is sum the values from A1 of each sheet if column B is Yes, if No then exclude from sum. If we assume I have the number 10 in A1 of each worksheet, I want a formula that will look at column B for a Yes then sum the value from the sheet name in the adjacent cell A, the sum will be in cell C2.
What I have so for is:
=IF(B2="Yes",INDIRECT("'"&A2&"'!A1"))+IF(B3="Yes",INDIRECT("'"&A3&"'!A1"))+IF(B4="Yes",INDIRECT("'"&A4&"'!A1")) etc,etc.
This formula does work but the list of sheets could be extensive and the formula could be very lengthy so I am wondering if there is a better formula that would achieve the same result.
Something like =IF(B2:B="Yes", INDIRECT("'"&A2:A&"'!A1")) although this does not work.
Any advise would be greatly appreciated
What I have so for is:
=IF(B2="Yes",INDIRECT("'"&A2&"'!A1"))+IF(B3="Yes",INDIRECT("'"&A3&"'!A1"))+IF(B4="Yes",INDIRECT("'"&A4&"'!A1")) etc,etc.
This formula does work but the list of sheets could be extensive and the formula could be very lengthy so I am wondering if there is a better formula that would achieve the same result.
Something like =IF(B2:B="Yes", INDIRECT("'"&A2:A&"'!A1")) although this does not work.
Any advise would be greatly appreciated
Sheet name | Include | Total from sheets |
Sheet2 | Yes | =IF(B2="Yes",INDIRECT("'"&A2&"'!A1"))+IF(B3="Yes",INDIRECT("'"&A3&"'!A1"))+IF(B4="Yes",INDIRECT("'"&A4&"'!A1")) |
Sheet3 | Yes | |
Sheet4 | No | |
Sheet5 | No | |
Sheet6 | No | |
Sheet7 | Yes | |
Sheet8 | No | |
Sheet9 | No | |
Sheet10 | Yes |