EXCEL URGENT HELP!

[Skpenu505]

Hi, 
I recently joined the Excel Community because I urgent need help from an Excel spreadsheet expert. what function or formula can I used to narrow(lookup) a specific expenses to a particular departmental number.  Also, what function can i used to calculate the total of all expenses coded to a particular department code and fund code.It would be a lot if I can get help on this issue.
 

 

[#2]

I am not sure if I got it correctly, if you want to sum the amount for all expenses with a specific fund code + department code (here is an example: =SUMIFS(G7:G19,C7:C19,701,B7:B19,B7) )

[#3]

@Ricardo Viana 

Omg you are the Champ! Thanks so much for helping! i got another question how can I make the total expense amount populate on page 1 for each individual fund code( if this could appear under the the pivot table on page 1). Also on "sheet 2& 3" i would like to narrow fund code to particular department codes-meaning when i fill in my department code on sheet(page) 1 cell B1, only fund codes related to that department will show in page 2 column B.

Sheet 3(fund code) has all the fund codes that relates to a particular department or group of departments.

[#4 · Accepted solution]

@Skpenu505Here's a way to have the data validation list change depending on the dept number entered, as you requested. It uses named ranges for each departments codes, and then a VLOOKUP function nested in a CHOOSE function to select the appropriate range name.

Let me know if you have questions.

[#5]

@Skpenu505   One of the neat things about Excel is that there often are multiple ways "to skin the cat"

I woke up thinking that there HAS to be a simpler way to give yourself different drop down selection items for each Dept Code. And then the use of INDIRECT occurred to me. So here's a revised sample of how you can accomplish the narrowing of the Fund Codes to only those relevant to the Dept that's been entered.

The formula (in the Validation dialog box) is

=INDIRECT(VLOOKUP($C$7,CodeLkUp,2))

 where CodeLkUp is the name given to a table of two columns that simply lists the dept number and then the name of the range associated with the relevant Fund Codes.

[#6]

Thank you @mathetes  @Ricardo Viana !! You've both being of great help. I appreciate you both. I believe the second part of my question wasn't clear. On "page 2&3"under Column G is the amount for each expense coded with the fund code on (column B), how can I transfer the total on column G for each fund code(column B) to appear on Page 1 preferably on cell O43 with each fund code number to appear alongside the total. 

Hope this is clearer.  Thanks!

[#7]

Not clear enough (not to me at any rate). Cell O43 for example is off the formatted area; did you mean O34?

That aside, though, it would help if you filled in one of the recent sheets with a few hypothetical numbers (on pages 2 and 3....and then describe more completely with multiple examples. Use Text boxes on each sheet, perhaps, to indicate which numbers go where.... and then upload that as yet another example.

[#8]

@mathetesSee the attachment.

[#9]

@Skpenu505    This is a bit tricky. I  invite people who know Power Query to step in here, as don't think I have that functionality--if I do I haven't found it--on my Excel for Mac, even though I'm fully up-to-date with the software.

I do know how to write queries using Excel's excellent Database functions (DSUM, for example) but not in a way that would be simple to implement. I have an invoicing system my wife uses for her consulting business, and it does what you're trying to do here, but with a much less extensive set of codes that serve the purpose of your Fund Codes here. I use DSUM to get the sum of all billable amounts (you'd be doing it for expenses) in each Code and then I just manually hide rows that are empty.

It's conceivable that a similar query plus a macro could do that for you....but I don't know how to write the macro.

In the meantime, while you wait for a Power Query solution (if there is one), why don't you research DSUM to see if you could craft your own solution. I will tell you that it's definitely possible.