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

C

cmartin2459

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. S
A5:A237 is a date, B5:D237 is a number based on the amount of wate
used for each shift for that day.

For each day of the month and for each shift our operators input th
water demand during their shift.

In my spreadsheet cell C1 looks for the highest number in the rang
B5:D462. That number becomes the value in C1. In cell C3 I want th
value to be the corresponding date from Column A. In other words, i
the highest number is 5 and it's in cell C6, then cell C1 = 5. A6 i
the corresponding date. I want C3 to equal A6.

I hope someone can understand my question and help me.

Thanks,
c
 
D

Danny Lewis

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
 
D

Danny Lewis

Sorry

=INDEX(Datatable,MATCH($C$1,C,0),1)

Danny Lewis said:
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
 
S

SimonCC

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top