• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUM with multiple criteria and sheets using Data Validation List.

Svet

Svet

New Member
Hi, and G'day from the Land Down Under. I'm exited to have finally registered and to be part of a great group of people. I too, (like member "GrassFarmer") am a sponge on almost anything Excel, except those items that are WAY over my head. I'd like to think of myself as an intermediate user. I can navigate fairly well through a spreadsheet, and always willing to learn more...more..more.

Now for my problem, my workbook has sheets named by month ex. Jan, Feb etc. they contain the data that is required.

I have another sheet as the interface from data to maybe a info sheet/dashboard.
The interface sheet has two tables so I can compare the months. The tables will have the criteria to search for, 1 column will have a list of transaction types and the top rows will have Week 1, Week 2 etc. the cells inbetween will have the following array formula which works fine.

{=SUM((SEP!$A$2:$A$200=$R$13)*(SEP!$C$2:$C$200=[@Data2])*SEP!$F$2:$F$200)}

Now what I want to do is use a Data Validation List of Months and a cell with formula =$S&7&"!" which will return for ex. SEP!

How can I use that cell in the main formula (highlighted) to make the two tables dynamic.

I have tried a few different ways using Indirect to no avail...please help.

If there is an easy alternative or solution to my query... advice would be appreciated.

Cheers :confused:

Good stuff Chandoo.
 
Hi ,

Suppose we assume that cell S7 has the data validation drop-down of the list of months ; when you select SEP from the list of months , S7 will contain the text SEP.

With this assumption , your posted formula will become :

=SUM((INDIRECT("'"&$S$7&"'"&"!"&"$A$2:$A$200")=$R$13)*(INDIRECT("'"&$S$7&"'"&"!"&"$C$2:$C$200")=[@Data2])*INDIRECT("'"&$S$7&"'"&"!"&"$F$2:$F$200"))

where the individual sections have been replaced :

SEP!$A$2:$A$200 has been replaced by INDIRECT("'"&$S$7&"'"&"!"&"$A$2:$A$200")

SEP!$C$2:$C$200 has been replaced by INDIRECT("'"&$S$7&"'"&"!"&"$C$2:$C$200")

SEP!$F$2:$F$200 has been replaced by INDIRECT("'"&$S$7&"'"&"!"&"$F$2:$F$200")

Narayan
 
Hi NARAYANK991...thanks for prompt response, I'll check it in the morning, on night shift at the moment :DD:DD:DD
 
NARAYANK991 said:
Hi ,

Suppose we assume that cell S7 has the data validation drop-down of the list of months ; when you select SEP from the list of months , S7 will contain the text SEP.

With this assumption , your posted formula will become :

=SUM((INDIRECT("'"&$S$7&"'"&"!"&"$A$2:$A$200")=$R$13)*(INDIRECT("'"&$S$7&"'"&"!"&"$C$2:$C$200")=[@Data2])*INDIRECT("'"&$S$7&"'"&"!"&"$F$2:$F$200"))

where the individual sections have been replaced :

SEP!$A$2:$A$200 has been replaced by INDIRECT("'"&$S$7&"'"&"!"&"$A$2:$A$200")
SEP!$C$2:$C$200 has been replaced by INDIRECT("'"&$S$7&"'"&"!"&"$C$2:$C$200")
SEP!$F$2:$F$200 has been replaced by INDIRECT("'"&$S$7&"'"&"!"&"$F$2:$F$200")

Narayan
Click to expand...

Hi NARAYANK991, you are a legend, it worked on first attempt, I tried something similar but got something back to front. Thanks heaps man :DD:DD:DD

Cheers
 
You must log in or register to reply here.
Back
Top