reporting newest/highest value
 

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

reporting newest/highest value
 

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
---
"Resi" wrote:
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.

reporting newest/highest value
 

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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

reporting newest/highest value
 

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


"Ashish Mathur" wrote:

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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

reporting newest/highest value
 

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

"Max" wrote:

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
---
"Resi" wrote:
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.

reporting newest/highest value
 

Hi,

Did you Ctrl+Shift+Enter the formula.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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


"Ashish Mathur" wrote:

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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

reporting newest/highest value
 

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" wrote:
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?

reporting newest/highest value
 

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

"Max" wrote:

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" wrote:
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?

reporting newest/highest value
 

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

"Ashish Mathur" wrote:

Hi,

Did you Ctrl+Shift+Enter the formula.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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


"Ashish Mathur" wrote:

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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

reporting newest/highest value
 

"Resi" wrote:
.. 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.

reporting newest/highest value
 

Hi,

Please mail me the file at

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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

"Ashish Mathur" wrote:

Hi,

Did you Ctrl+Shift+Enter the formula.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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


"Ashish Mathur" wrote:

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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

reporting newest/highest value
 

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

"Ashish Mathur" wrote:

Hi,

Please mail me the file at

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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

"Ashish Mathur" wrote:

Hi,

Did you Ctrl+Shift+Enter the formula.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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


"Ashish Mathur" wrote:

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Resi" wrote in message
...
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