A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Automatically insert a date value based on the value from another cell



 
 
Thread Tools Display Modes
  #1  
Old July 19th 06, 03:11 PM posted to microsoft.public.excel.worksheet.functions
cmartin2459
external usenet poster
 
Posts: 1
Default 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  
Old July 19th 06, 03:26 PM posted to microsoft.public.excel.worksheet.functions
Danny Lewis
external usenet poster
 
Posts: 44
Default 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  
Old July 19th 06, 03:29 PM posted to microsoft.public.excel.worksheet.functions
Danny Lewis
external usenet poster
 
Posts: 44
Default 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  
Old July 19th 06, 04:23 PM posted to microsoft.public.excel.worksheet.functions
cmartin2459
external usenet poster
 
Posts: 1
Default 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  
Old July 19th 06, 04:50 PM posted to microsoft.public.excel.worksheet.functions
Danny Lewis
external usenet poster
 
Posts: 44
Default 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  
Old July 19th 06, 06:29 PM posted to microsoft.public.excel.worksheet.functions
SimonCC
external usenet poster
 
Posts: 79
Default 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 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 = 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  
Old July 19th 06, 07:18 PM posted to microsoft.public.excel.worksheet.functions
cmartin2459
external usenet poster
 
Posts: 1
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can Excel automatically insert current date in a cell? AdrianXing Excel Worksheet Functions 23 March 12th 14 08:35 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


All times are GMT +1. The time now is 08:00 PM.


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