Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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:D = 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, B5:D237 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 B5:D462. 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert a date value based on the value from another
Hi
Name range A4:D237 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:D = 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, B5:D237 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 B5:D462. 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 A4:D237 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:D = 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, B5:D237 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 B5:D462. 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert a date value based on the value from another
Try this formula:
=INDEX(A5:A237,MAX((ROW(B5:D237)-ROW(B5)+1)*(B5:D237=MAX(B5:D237)))) Note that this is an array formula, so instead of just hitting Enter after typing the formula, you should use Ctrl-Shift-Enter. 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:D = 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, B5:D237 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 B5:D462. 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel automatically insert current date in a cell? | Excel Worksheet Functions | |||
Update cell based on date range | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |