Forum Discussion
EXCEL URGENT HELP!
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.
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) )
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.
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.
@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.
