Forum Discussion

Skpenu505's avatar
Skpenu505
Copper Contributor
Nov 13, 2019
Solved

EXCEL URGENT HELP!

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.
 

 
  • 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.

8 Replies

  • 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) )

    • Skpenu505's avatar
      Skpenu505
      Copper Contributor

      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.

      • mathetes's avatar
        mathetes
        Silver Contributor

        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.

Resources