Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 8th 05, 06:31 PM posted to microsoft.public.excel.worksheet.functions
Jeff
 
Posts: n/a
Default 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   Report Post  
Old December 8th 05, 06:53 PM posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default 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   Report Post  
Old December 8th 05, 06:54 PM posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default 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   Report Post  
Old December 9th 05, 12:16 AM posted to microsoft.public.excel.worksheet.functions
Jeff
 
Posts: n/a
Default 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   Report Post  
Old December 9th 05, 03:32 PM posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default 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   Report Post  
Old December 9th 05, 07:14 PM posted to microsoft.public.excel.worksheet.functions
Jeff
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change cell color dependin on date ... T. Denford Excel Worksheet Functions 4 April 13th 05 01:59 AM
Changing Cell formats to date fields automatically PCLIVE Excel Worksheet Functions 3 April 12th 05 10:34 PM
How do you continuously update time and date in an Excel cell? engine99 Excel Worksheet Functions 7 February 21st 05 03:20 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
Using formulas to determine date in one cell based on date in anot Gary Excel Worksheet Functions 2 November 22nd 04 09:11 AM


All times are GMT +1. The time now is 07:39 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017