For the life of me I couldn’t figure out a search string to address this so apologies if it’s been done.
I have a budget workbook consisting of 4 tabs: 1 for each of 3 departments and 1 that pulls data from the others to let you compare departments side by side.
A sample version with only the comparison tab and 1 short department tab is here: https://docs.google.com/spreadsheets/d/1KeJFG_fqKNNBxT5dSkSWjk_6t0CgMcp6_3sHE5NBoMo/edit?usp=sharing
On ‘Projection Comparison’ you select a month and year in E1 & E2 that then concatenate into a date in E3. I need to pull data from ‘Department 1’!A:M based on that date.
I’m currently using a static list of contract names on ‘Projection Comparison’ and using index+match to pull in the budget projection data from ‘Department 1’ that matches the date in E3.
However, this leaves me with contracts with blank budget projections for some months, and I’d much rather only have contracts listed that have projections for that month.
I need ‘Projection Comparison’!B5:B9 to populate with ‘Department 1’!A2:A6, but only if there is a budget projection in ‘Department 1’!B2:M6 in the column with a header that matches the date in ‘Projection Comparison’!E3.
I also need C5:C9 to populate with said budget projection from tab ‘Department 1’, but again, only if it isn’t blank.
I’ve tried several combinations of Query, ArrayFormula, Filter, Index+Match, but I lack the expertise to get it right.
Help?
>Solution :
delete all in B5:C10 and use this in B5:
=FILTER({'Department 1'!A2:A6,
FILTER('Department 1'!B2:6, 'Department 1'!B1:1=E3)},
FILTER('Department 1'!B2:6, 'Department 1'!B1:1=E3)<>"")
