Formula to look at specific column/cell depending on the current month

kelceyg

Hello,

I want to create a dashboard widget that will show employee FTEs for the current month. I currently have a sheet (Capacity Plan) that list out the employee's name, and then has the assigned projects with FTE breakdown as child rows underneath. Then I have row that calculates the total for the month.

For the widget, I would like to create a separate sheet with a formula that says "If the employee is Sarah and the month is January look at this cell in the JAN 23 column, If the employee is Sarah and the month is February look at this cell in the FEB 23 column..."

I wasn't sure if I could use an IF + VLOOKUP or INDEX/MATCH.

If it's no possible, I welcome any other suggestion to avoid having to manually change the cell link every month. It's not the end of the world, but the more I can automate, the better!

Thank you in advance!

Paul Newcome

Are you wanting to pull for the current month?

kelceyg

Yes, I would like to pull the current month's allocations for each team member. I am hoping there is a way that a formula can do this automatically so I don't have to update something manually each time they're assigned a new project or at the beginning of each month.

I would then have it feed a widget on the dashboard to show the team member's name and their current allocation.

Paul Newcome

You would use something like this:

=INDEX({Range Covering Jan 23 - Dec 23 Columns}, MATCH([Employee Name]@row, {Column TO Match Employee Name In}, 0), MONTH(TODAY()))

kelceyg

Hi Paul,

I tried using the formula you suggested, but it didn't bring back anything.

=INDEX([JAN 23]:[DEC 23], MATCH([Project Name]@row, [Project Name]:[Project Name], 0), MONTH(TODAY()))

I think it might have to with the rows I had hidden. Here's a full view of the sheet:

Additionally, I don't know how the formula knows that for March, it needs to look at the Total Allocation cell in the MAR 23 column.

Paul Newcome

Where exactly are you putting this formula?

kelceyg

I added a column and put it in the same row as Sarah's name (third row).

Paul Newcome

Ah. Ok. Try this one:

=INDEX(COLLECT([JAN 23]:[DEC 23], [Project Name]:[Project Name], @cell = "Total Allocation", [Assigned To]:[Assigned To], @cell = [Project Name]@row), 1, MONTH(TODAY())

kelceyg

I got the error Incorrect Argument Set.

Paul Newcome

And that is to be expected because I didn't think it through all the way. My apologies. Try this one instead...

=INDEX([JAN 23]:[DEC 23], MATCH("Total Allocation", [Project Name]:[Project Name], 0), MONTH(TODAY())

If you have more than just Sarah on the same sheet though, we may need to try something a little different. Insert a helper column (it can be hidden after setting it up) and use this column formula:

=INDEX([Jan 23]@row:[Dec 23]@row, 1, MONTH(TODAY())

Then in the call you are wanting to have the actual output in you would use:

=INDEX(CHILDREN([Helper Column]@row), MATCH("Total Allocation", CHILDREN([Project Name]@row), 0))