If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Automatically insert a date value based on the value from another cell
I have a spread sheet that has the following info: Column A = Date, Columns B = times associated with shifts, i.e. B4 = 11 p.m.  7 a.m.; C4 = 7 a.m.  3 p.m.; D4 = 3 p.m.  11 a.m. So A5:A237 is a date, B5237 is a number based on the amount of water used for each shift for that day. For each day of the month and for each shift our operators input the water demand during their shift. In my spreadsheet cell C1 looks for the highest number in the range B5462. That number becomes the value in C1. In cell C3 I want the value to be the corresponding date from Column A. In other words, if the highest number is 5 and it's in cell C6, then cell C1 = 5. A6 is the corresponding date. I want C3 to equal A6. I hope someone can understand my question and help me. Thanks, cj  cmartin2459  cmartin2459's Profile: http://www.excelforum.com/member.php...o&userid=36530 View this thread: http://www.excelforum.com/showthread...hreadid=562866 
Ads 
#2




Automatically insert a date value based on the value from another
Hi
Name range A4237 as DataTable Name the ranges of data in columns B to D whatever you like (for this leave them as B to D) =INDEX(Datatable,MATCH(C6,C,0),1) Should be ok Danny "cmartin2459" wrote: > > I have a spread sheet that has the following info: > Column A = Date, Columns B = times associated with shifts, i.e. B4 = > 11 p.m.  7 a.m.; C4 = 7 a.m.  3 p.m.; D4 = 3 p.m.  11 a.m. So > A5:A237 is a date, B5237 is a number based on the amount of water > used for each shift for that day. > > For each day of the month and for each shift our operators input the > water demand during their shift. > > In my spreadsheet cell C1 looks for the highest number in the range > B5462. That number becomes the value in C1. In cell C3 I want the > value to be the corresponding date from Column A. In other words, if > the highest number is 5 and it's in cell C6, then cell C1 = 5. A6 is > the corresponding date. I want C3 to equal A6. > > I hope someone can understand my question and help me. > > Thanks, > cj > > >  > cmartin2459 >  > cmartin2459's Profile: http://www.excelforum.com/member.php...o&userid=36530 > View this thread: http://www.excelforum.com/showthread...hreadid=562866 > > 
#3




Automatically insert a date value based on the value from anot
Sorry
=INDEX(Datatable,MATCH($C$1,C,0),1) "Danny Lewis" wrote: > Hi > > Name range A4237 as DataTable > Name the ranges of data in columns B to D whatever you like (for this leave > them as B to D) > > =INDEX(Datatable,MATCH(C6,C,0),1) > > Should be ok > > Danny > "cmartin2459" wrote: > > > > > I have a spread sheet that has the following info: > > Column A = Date, Columns B = times associated with shifts, i.e. B4 = > > 11 p.m.  7 a.m.; C4 = 7 a.m.  3 p.m.; D4 = 3 p.m.  11 a.m. So > > A5:A237 is a date, B5237 is a number based on the amount of water > > used for each shift for that day. > > > > For each day of the month and for each shift our operators input the > > water demand during their shift. > > > > In my spreadsheet cell C1 looks for the highest number in the range > > B5462. That number becomes the value in C1. In cell C3 I want the > > value to be the corresponding date from Column A. In other words, if > > the highest number is 5 and it's in cell C6, then cell C1 = 5. A6 is > > the corresponding date. I want C3 to equal A6. > > > > I hope someone can understand my question and help me. > > > > Thanks, > > cj > > > > > >  > > cmartin2459 > >  > > cmartin2459's Profile: http://www.excelforum.com/member.php...o&userid=36530 > > View this thread: http://www.excelforum.com/showthread...hreadid=562866 > > > > 
#4




Automatically insert a date value based on the value from another cell
I tried your suggestion Danny. But it doesn't like the second C.  cmartin2459  cmartin2459's Profile: http://www.excelforum.com/member.php...o&userid=36530 View this thread: http://www.excelforum.com/showthread...hreadid=562866 
#5




Automatically insert a date value based on the value from anot
You need to rename the range C5:C237 to C, for example.
"cmartin2459" wrote: > > I tried your suggestion Danny. But it doesn't like the second C. > > >  > cmartin2459 >  > cmartin2459's Profile: http://www.excelforum.com/member.php...o&userid=36530 > View this thread: http://www.excelforum.com/showthread...hreadid=562866 > > 
#6




Automatically insert a date value based on the value from another
Try this formula:
=INDEX(A5:A237,MAX((ROW(B5237)ROW(B5)+1)*(B5237=MAX(B5237)))) Note that this is an array formula, so instead of just hitting Enter after typing the formula, you should use CtrlShiftEnter. Excel will automatically put { } around the formula if done correctly Simon "cmartin2459" wrote: > > I have a spread sheet that has the following info: > Column A = Date, Columns B = times associated with shifts, i.e. B4 = > 11 p.m.  7 a.m.; C4 = 7 a.m.  3 p.m.; D4 = 3 p.m.  11 a.m. So > A5:A237 is a date, B5237 is a number based on the amount of water > used for each shift for that day. > > For each day of the month and for each shift our operators input the > water demand during their shift. > > In my spreadsheet cell C1 looks for the highest number in the range > B5462. That number becomes the value in C1. In cell C3 I want the > value to be the corresponding date from Column A. In other words, if > the highest number is 5 and it's in cell C6, then cell C1 = 5. A6 is > the corresponding date. I want C3 to equal A6. > > I hope someone can understand my question and help me. > > Thanks, > cj > > >  > cmartin2459 >  > cmartin2459's Profile: http://www.excelforum.com/member.php...o&userid=36530 > View this thread: http://www.excelforum.com/showthread...hreadid=562866 > > 
#7




Automatically insert a date value based on the value from another cell
Okay, I'll try that. Thanks.  cmartin2459  cmartin2459's Profile: http://www.excelforum.com/member.php...o&userid=36530 View this thread: http://www.excelforum.com/showthread...hreadid=562866 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Can Excel automatically insert current date in a cell?  AdrianXing  Excel Worksheet Functions  24  March 23rd 15 01:39 PM 
Update cell based on date range  deversole  Excel Discussion (Misc queries)  3  July 6th 05 01:58 PM 
Possible Lookup Table  Karen  Excel Worksheet Functions  5  June 8th 05 09:43 PM 
Addition to Turn cell red if today is greater or equal to date in cell  Rich  New Users to Excel  2  December 9th 04 02:06 AM 
Using formulas to determine date in one cell based on date in anot  Gary  Excel Worksheet Functions  2  November 22nd 04 08:11 AM 