reporting newest/highest value

[Resi]

Hi
hope you can help me!
is there a formula which allows me to update a cell in a different worksheet
in the same workbook, with the latest/highest value entered in a different
worksheet?

example:
worksheet B has got chronological listing of mileage related to different
vehicles and drivers.
in worksheet A I like to have the vehicle in cell A2 with it's latest
mileage reading in cell B2, where the criteria will be cell A2.

phew hope this makes sense!

many thanks, Resi

 

[Max]

One way

Source data assumed in sheet: B, cols A to C
where
col A = real dates
col B = vehicle ids
col C = mileage figs

In sheet: A,
With vehicle ids listed in A2 down
Put in B2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula
=INDEX(B!C$2:C$10,MATCH(MAX(IF(B!B$2:B$10=A2,B!A$2:A$10)),IF(B!B$2:B$10=A2,B!A$2:A$10),0))
Copy down to extract the required latest mileage fig (by the dates in B's
col A) for the vehicle ids listed in A2 down. Adapt the ranges to suit the
extents of your actuals.

Real dates are just nums, so the "max" date for that vehicle id = latest date
High-five? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Ashish Mathur]

Hi,

This array formula (Ctrl+Shift+Enter) will give you the maximum value

=MAX(IF((Sheet2!$B$5:$B$9=Sheet1!A3),Sheet2!D59)). Cell A3 has A

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Resi]

hi i tried to implement your formula but it does not report the highest
mileage or does it for all vehicles.

in sheet 2, i have the vehicles IDs in column B5:B1000 and their mileage in
column C5:C1000, these are in chronological order.

in sheet 1 i have the ID in column A and the respective mileage in column B,
in this sheet i should have the vehile ID appearing once and the latest
mileage reading for that vehicle.

the formula i am using is:

=MAX(IF((sheet2!$B$5:$B$100=sheet1!A5),sheet2!$C$5:$C$100))

where am i going wrong?
thanks R

 

[Resi]

hi Max
thanks for your answer! took me a while to work out what it was! but, still
gives me an asnwer as n/a...
any idea?
r

 

[Ashish Mathur]

Hi,

Did you Ctrl+Shift+Enter the formula.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Max]

gives me an answer as n/a ...

That's because you didn't confirm the formula correctly. "Array-enter" means
to press CTRL+SHIFT+ENTER (CSE) to confirm the formula, instead of just
pressing ENTER. When it's done correctly, Excel will wrap curly braces { }
around the formula, which you can see inside the formula bar. Do sight this
as a visual check that the formula is correctly array-entered. If you don't
see the curlies, then it isn't array-entered, and it won't return the correct
results. Click inside the formula bar again, and re-do the CSE. Then re-check
for the curlies. It should work fine.

Celebrate success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Resi]

i did I did! ;o)
yes the curlies are there in but still n/a as an answer...

 

[Resi]

yup dones...curlies all accounted for but no outcome in the cell.
any other ideas?
ta, R

 

[Max]

.. yes the curlies are there in but still n/a as an answer...

Then it points to your data quality. Either you don't have real* dates in
B's col A and/or your vehicle ids** are not consistent between sheets A and
B.

Here's a sample to demonstrate the earlier in pristine working order:
http://freefilehosting.net/download/45hf6
Auto-reporting the latest value by date by vehicle id.xls

Take it away, convince yourself that it works

*You can convert text dates at one go to real dates using Data>Text to
Columns. Select B's col A, click Data>Text to Columns. Click Next>Next. In
step3 of the wizard, under Col data format, check "Date", choose the
appropriate format from the droplist, eg: MDY. Click Finish.

**Other than typos, the likely cause for data inconsistency would be the
invisible white spaces here and there within the data which throws correct
matching off. Use TRIM/copy n paste special as values to clean up both cols
of vehicle ids in sheets A and B.

Celebrate success, finally? Click YES below and in ALL earlier responses.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

 

[Ashish Mathur]

Hi,

Please mail me the file at (e-mail address removed)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Resi]

Hi Ashish
i have been away hence the silence. whilst away i 'tinkered' with the
formula. and i am not100% sure how/why but now it is working! so many many
thanks for your help!
ttfn, Resi