CODEWITHSUNDEEP
excel
Takeshi Patterson
Takeshi Patterson

Reputation: 1277

Is it possible to output the values of a column in excel based on a dynamic date value?

In the following table I have weeks as columns, employees as rows and the values are the amount of holiday days per employee per week taken.

enter image description here

I want to create a separate table as a dashboard where the output will change depending on the date I input into the date selector. In the example below, I have chosen the 2/11/2015 as my week and I would like to only see the corresponding values for that week in the output table. How do I go about doing this?

enter image description here

Upvotes: 0

Views: 36

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Try this in B5:

=Index($B$34:$F$48,MATCH($A5,$A$34:$A$48,0),MATCH($C$1,$B$33:$F$33,0))

Then copy down.

Change the F column References in the formula to get the extent of you columns of data.

As a Note: If you are in a country that use ; as the delimiter between criteria instead of , here is the formula with those delimiters:

=Index($B$34:$F$48;MATCH($A5;$A$34:$A$48;0);MATCH($C$1;$B$33:$F$33;0))

Upvotes: 1

Related Questions