Totaling a cell in multiple sheets if a different cell meets a specific requirement

[corvusbio]

I am new to excel - forgive me if this is a basic question. I have spent a few hours searching the fourm and I have not found a solution.

I am trying to make a custom invoice using excel. Contractors who are fulfilling different roles will be using the same exact template (excel sheet) to report times. The different roles are selected using a drop-down menu (C4). The total for hours are reported in cell AX26. Each submitted invoice will become a sheet in a workbook that represents the final invoice going to the client. There are 20 sheets, named 1-20. There will be a summary sheet that totals the sum of hours for each role.


Invoice users have 4-options from the drop-down menu in C4: Authorized Biologist, General Monitor, Bighorn Sheep Monitor, Avian Biologist. The drop-down menu in C4 allows users to choose from a list that references a source, within each sheet, as $BB$2:$BB$5.
$BB$2 = Authorized Biologist
$BB$3 = General Monitor
$BB$4 = Bighorn Sheep Monitor
$BB$5 = Avian Biologist.

I would like the summary sheet will contain totals for each of these positions.

Beginning with, "Authorized Biologist" I am trying to write a formula that will total the sum of AX26 for sheets:1-20 that contain, "authorized biologist" in cell C4, while filtering out totals for the other roles.

My lame attempt (failed) looks something like this:
=SUM('1:20'!AX26),IF('1:20'!C4)='1'!BB2

The last part : ='1'!BB2, references a sheet, "1" as the qualifier for C4 because not all of the sheets have an option for BB2 - Authorized Biologist...

Your asssistance is very much appreciated

[FDibbins]

welcome to the forum,

well for a start off, you could just use 1 reference source, give it a named range and then in DV use =named-range

apart from that, its hard to say. what might be an option, is on each sheet, wayyyyyy off to the side, have those 4 titles, then use sumif() to get a total for them (not sure, but i dont think sumifs works across sheets). then on your master, just sum those 4 values across all sheets. something like =SUM('sheet2:Sheet10'!B4) repeated for each title.

if you upload a sample workbook, i can help you get it set up

[corvusbio]

FDibbins,

Thanks for the quick repy!

I am such an Excel rookie, I don't know what you meant by, "give it a named range and then in DV use =named-range."

What is DV? Is this data validation?

I tried using the "SUMIF" function with: =SUMIF('1:20'!C4,'1'!BB2,'1:20'!AX26) I get a, "value" error, probably because C4 and '1'!BB2 are words (Authorized Biologist) and not numbers...

I have attached a sample of the workbook:NEW_Practice_Invoice.xlsx

Thanks!

[FDibbins]

take a look at the attached and see if this is what you want?

yes DV is data validation, and you will see on sheet1 i added all 4 categories, and gave them a named range.

you can create a named range by highlighting a cell or range of cells, right clicking and selecting "name a range" once you have a range named, you can reference the name instead of the range, so i called that range Options, and then applied it to all of your DV cells. go check them out
you can also use named ranges in formulas, such as =sum(hours) if you had a range named hours

[corvusbio]

Practice_Invoice_1_Correct.xlsxFDibbins,

That's it! You hit it out of park. Thanks for your feedback.

I am slowly beginning to understand the application for DV and "named ranges."

The solution to my original problem has opened a door to a series of related and unrelated questions, as I attempt to apply these concepts to other areas of the workbook.

First, can you help me understand the syntax to the equation that wrote: =Indirect(""&$W3&"!C4").

Additional questions (probably more appropriate in a different thread(s):

1. I've modified the "options" list to include, "biological monitor." Is there some way to add this new position to the already created "options" named-range that you created? I was able to add the position and create a new named-range (I called it Positions). In doing so, I had to change my DV cells to incorporate the change. C4 on sheets 1 and 2 contain this DV mod (named range is "Positions", changed from "Options"). This leads me to my next question

2. If I want to change the named range in cell C4 on sheets 1 - 20, do I have to manually do it one cell at a time or is there some way that I can make the change once and have it apply to cell C4 on sheets 1-20?

I've applied VLOOKUP to Cell G4 (hourly rate) on sheet 1. Now, the hourly rate changes when a different position is selected (cell C4). Related to question 2 above - I need to apply this formula to cells: G6, G8, G10, G12... G22 on sheets '1:20'! Is there some way to write this in without having to enter the formula (or copy and paste) once cell at a time (180 times)?

3. There is a task code list on the "summary" sheet (B4:B39). Similar to the original issue, is there a formula that will search sheets 1-20 and apply the correct sum for all of the task codes throughout the workbook? Sheets 1-20 will be contactor invoices and there is space for 3-additional task codes to be entered by the user. The white cells will be locked, so there are 6 task codes (the most commonly-used) that will be static and I know the basic formula to calculate sums for these (as displayed in Summary!C4). However, it would be really nice if the summary page would automatically sum the "wildcard" task codes that could be written into '1:20'!E18:E22.

4. OK finally -- I would like to use the same summary page, month after month. I will have a different number of contractors coming and going from the project, so the number of invoices (sheets) that I receive each month will change. I will never have more than 20 sheets per invoice and the sheet layout will not change. Is there some way for the formulas on the summary page to remain static and continue to apply the proper calculations as different sheets are deleted and copied into the workbook? Does the, "$" symbol incorporated into formulas accomplish this?

Thanks for your time.

On a tangent,

Corvusbio