3D Formula...
Posted by Ian Mac on January 30, 2002 4:21 AM
All,
can anyone tell me how I might do this,
10 Sheets
Sheet1 A1: A B1: 2.5
Sheet2 A1: B B1: 1.5
Sheet3 A1: A B1: 3.75
Sheet4 A1: C B1: 4
Sheet5 A1: B B1: 2.5
Etc.
Etc.
Etc.
Etc.
Etc.
I need to add B1 where A1 = Input Value (eg A)
in this case A would result in 6.25
Many Thanks
Ian Mac
Posted by Bessie Bagshot on January 30, 2002 5:29 AM
SUMIF cannot be used across sheets.
Assuming you want to put the result in Sheet1, a workaround would be to select all sheets except Sheet1 and enter the following formula in cell C1 (or the cell of your choice) :-
=IF(A1="A",B1,0)
Then enter in C1 on Sheet1 :-
=IF(A1="A",B1,0)+SUM(Sheet2:Sheet10!C1)
Posted by Ian Mac on January 30, 2002 7:05 AM
My idea is to have it in one cell on a MasterSummary so i then manipulate the formula outside of the cell
i.e. change the value in a1 on the summary to "B" and it would add the accordingly,
unfortunatly the things it needs to look at are not just A B C, they will eventually be a lot more dynamic, pulling the A B C (as it were) from lookups on a Data sheet which inturn would drive a drop down on the summary to work from,
any clearer (it's not to me)
Ian Mac
Posted by Bessie Bagshot on January 30, 2002 3:40 PM
>>>>My idea is to have it in one cell on a MasterSummary so i then manipulate the formula outside of the cell
i.e. change the value in a1 on the summary to "B" and it would add the accordingly,
Just change the formula on Sheets 2 thru 10 to :-
=IF(A1=Sheet1!A1,B1,0)
On Sheet1, if you don't want to include Sheet1 in the total :-
=SUM(Sheet2:Sheet10!C1)
>>>>unfortunatly the things it needs to look at are not just A B C, they will eventually be a lot more dynamic, pulling the A B C (as it were) from lookups on a Data sheet which inturn would drive a drop down on the summary to work from,
Not enough info to respond. Sounds like VBA may be required.
