Update a cell with a specific value related to a date.
 

I have an array of data in a 2 dimisional matrix

X = date value
Y = Individual contributor

In the Matix I have plotted the expected amount of work left by an
individual on a specific date.

I would like to create a formula that can look at the date value in row 2
and return the correct value for the individual contributor on that specific
date.

Any Ideas


--
Jeff

Update a cell with a specific value related to a date.
 

Assume dates are entered in B1:F1 and individuals in A2:A10 so that the
work remaining is in B2:F10. If X and Y are the values you want to
find:

= INDEX(MATCH(Y,A2:A10,0),MATCH(X,B1:F1,0))

MATCH finds the position of each value in each ray (0 means match
exactly, see help for other options)

INDEX finds the intersection of the two positions.

Hope that helps

- John
www.JohnMichl.com

Update a cell with a specific value related to a date.
 

Jeff,
I assume your date and contributor lookup values are in K1 and L1
respectively. I further assume that your table occupies the range
A2:F10 and that date headings appear in row 2 as you indicate in your
post. Change as necessary.

=VLOOKUP(L1, A2:F10, MATCH(K1, A2:F2, 0), 0)

HTH
Kostis Vezerides

Update a cell with a specific value related to a date.
 

So I would like to update the second column (avaliable) with the data from
the date columns automatically on a give day. In this instance on 12/5 the
avalible column should be A=25, B=37, C=44, D=39, E=35,F= 22

on 12/6 the values should auto update to A=24, B=36, C=42, D=38, E=34,F= 21


Redmond
Test Available 12/3 12/4 12/5 12/6 12/7
A 22 12% 38 25 25 24 23
B 33 19% 58 39 37 36 35
C 39 22% 67 45 44 42 40
D 35 19% 60 40 39 38 36
E 31 17% 54 36 35 34 32
F 19 11% 34 23 22 21 20
Goal 179 309.96 208.46 201.21 193.96 186.71

I don't see where the automatic update feature would factor into your
equation. I Probably didn't explain myself very well.
--
Jeff


"vezerid" wrote:

Jeff,
I assume your date and contributor lookup values are in K1 and L1
respectively. I further assume that your table occupies the range
A2:F10 and that date headings appear in row 2 as you indicate in your
post. Change as necessary.

=VLOOKUP(L1, A2:F10, MATCH(K1, A2:F2, 0), 0)

HTH
Kostis Vezerides

Update a cell with a specific value related to a date.
 

I think I see where you are going. Let's try this.
Assume the word "Available" is in cell B2. Move it to B1 and put the
date 12/7 in B2. In cell B3 enter the formula,

index(E3:H3,1,MATCH($B$2, $E$2:$H$2))

Copy this formula down and you should be good to go.

- John

Update a cell with a specific value related to a date.
 

I think I am doing a terrible job of explaining what I am trying to accomplish.

Let me give a little back ground.

A) I have five developers, at the begining of a project they gave me an
estimate of the amount of time they could each devote to the project. On the
last day of the project there would be 0 avalible hours left.

B) Based on this data and the duration of the project I then calculated the
amount of avalible work left per developer per day.

C) Since I have calculated the amount of work avalible per dev per day in
the array I would like to come up with a formula that would update the
available column each day with the already calculated value for each Dev. I
am making the assumption that somewhere I will need to use the Today()
function to trigger which value gets placed in the avalible column.
--
Jeff


"John Michl" wrote:

I think I see where you are going. Let's try this.
Assume the word "Available" is in cell B2. Move it to B1 and put the
date 12/7 in B2. In cell B3 enter the formula,

index(E3:H3,1,MATCH($B$2, $E$2:$H$2))

Copy this formula down and you should be good to go.

- John