Home 
Search 
Today's Posts 
#1




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 
#2




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 
#3




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 
#4




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 
#5




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 
#6




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Change cell color dependin on date ...  Excel Worksheet Functions  
Changing Cell formats to date fields automatically  Excel Worksheet Functions  
How do you continuously update time and date in an Excel cell?  Excel Worksheet Functions  
GET.CELL  Excel Worksheet Functions  
Using formulas to determine date in one cell based on date in anot  Excel Worksheet Functions 