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 |
All times are GMT +1. The time now is 09:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com