Populating data based on department choice

[Nina579]

Hi,

I am working on a budget file. It's a huge workbook with a multitude of data.

I want to create a summary sheet, where you can choose a single department and see its detailed budget. Of course we could just use filters, but in the summary we only want to see selected data (about 3 columns from the total of 50...), so it's much easier to create a separate sheet with just the needed data. Anyway, I created some pivots and used VLOOKUP to make a summary page with 1. initial budget 2. total spending 3. balance 4. details. Since the first 3 are singular cells, VLOOKUP deals with it just fine, but details are more tricky. It's about 20-30 rows of data for each department, and VLOOKUP returns only the first one. Is there any way to auto-populate data in "details", depending on the department chosen from the dropdown list?

I can't share this file here, but it looks something like this:
1. department: [dropdown list to choose department - cell D3]
2. initial budget: =IFERROR(VLOOKUP(D3;'budget2016'!A:B;2;0); "[choose department]")
3. total spending: =IFERROR(VLOOKUP(D3;'totalspending'!A:B;2;0); "[choose department]")
4. balance: =IFERROR(SUM(D4-D5); "[choose department]")
5. details: ???????????

Also, probably worth mentioning - macros are disabled in my company because of security measures, and I can't turn them on, so if there is any way to do it with a formula, that would be great.

All ideas much appreciated!

[Glenn Kennedy]

=INDEX(BUDGET2016!B:B,SMALL(IF(BUDGET2016!$A$2:$A$1000=$D$3,ROW(BUDGET2016!$A$2:$A$1000)),ROWS($1:1)))

Where:
BUDGET2016!B:B is the entire column containing the data you want to return (Initial Budget??)
BUDGET!2016$A$2:$A$1000 are the rows containing the department names and
$D$3 is the Department name you're trying to use.

this is an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Nina579]

Hmmm I did everything you said, and the { } quotes appeared but the formula just returns "0" (even though the value it should return is actually text and not number)
Any idea what might be wrong?

[Nina579]

ok I realized that I should change D3 to C3 cause I actually deleted one column, but that aside, now it returns one random value from all the items in the list, it's not even the first one, just one from the middle of the list...

[Glenn Kennedy]

You'll have to cut your sheet down to a sample and post it.Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

[Nina579]

thanks for the hints on posting i did a quick sample version of what I had at work.

[Glenn Kennedy]

Here you go. I killed some merged cells in your sheet. These are totally best avoided. not only are they the work of Satan, they make compiling formulae very difficult. Keep them for fancy titles and NEVER put them in your data.

that said, the formula that I suggested is now working. I still have no idea what you did earlier; but Hey! It seems OK now.

[Nina579]

You. Are. Awesome. I am at the very beginning of my path to Excel-lence, so I know only the basics. I can't remember what I did before either, I don't have the work file in front of me anymore, but I think I must have messed up the cell references tbh.

One last question regarding this file - is it possible to hide the details and make them appear only if someone wants them to? I know this can be done with the click buttons from the developer tab, but I'd need a macro to program them, and as I mentioned before my firm doesn't allow that. Is there any way to do that without a macro?

(I'll avoid merged cells from now on, thanks for the tip )