All time best figure

[xenon325i]

Hi all

I have a sheet that records the production output from various machines that is automatically updated every 10 minutes. As well as displaying today's total I want to display the "all time" best total but I cant' seem to do this without creating a circular reference.

e.g. Cell C4 contains today's total from one machine, I want E4 to be the "best ever" and I'm using G4 as a hidden cell for my initial best ever which I've just entered 1

So, my best ever formula is =IF(C4>G4,C4,G4) so if today's is better that the previous best make the previous best = today's total. That's OK but how do I update G4 to be the best so far?

Hope that makes sense....

[AliGW]

Welcome! It would be easier to advise if we could have a look at your set-up.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[xenon325i]

Thank you Ali

Here is a sample:

[AliGW]

I'm sorry, but your sample sheet needs to be standalone. At the moment it's referencing this: ='C:\Program Files (x86)\JetReports\JetReports.xlam'!NL("Count","Capacity Ledger Entry",,"No.","SAW","Posting Date",TODAY())

So there's nothing I can do with it to troubleshoot your problem.

[xenon325i]

Sorry, I forgot to paste values only. Try this:

[AliGW]

I'm sorry - I'm not making myself clear! We need a simplified version of your data with calculations also present.

[Crooza]

Unless I'm missing something isn't it just

=MAX($B$4:B4) in D4 dragged down

[xenon325i]

Hi again

No, I don't want to see which individual stage has the maximum output of all, I want to see each stage's best-ever total individually.

@ Ali, sorry I copied and pasted values and forgot the other formula... duh. Try again...

[popipipo]

This is how I think it should be.

[xenon325i]

Hi Willem

Thank you. Is there a way that the update macro can be triggered without a user having to press a button? My intention is this will be an automated report that will run on a schedule. For example, can the macro be triggered if the workbook is closed thereby updating the best figure for the next time the workbook is opened?

[popipipo]

Try it this way

[xenon325i]

Hi Willhem. That seems to have broken the correct operation for me. As soon as I update a figure in the "today" column, the all-time best matches it but it no longer retains the highest value. If I enter 5 in today the highest goes to 5 but then if I enter 4 in today the highest also changes to 4 instead of remaining at 5. Thanks for your help so far

[popipipo]

If I enter 5 in today the highest goes to 5 but then if I enter 4 in today the highest also changes to 4

'Last time best' best is updated when file is closed
'All time best' is update if today is changed.

The 5 is not updated if the file is not saved closed

[xenon325i]

Ah, that makes perfect sense, thank you very much!!

One last question (I'm far from an Excel expert) how do i get the macro from this sample and put it into my working workbook?

[popipipo]

Open VBA:

Tab 'developer '
'Visual Basic'

or
ALT F11

[AliGW]

I am still very confused by your sample data.

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G
3		Today		All-time Best
4	STAGE 1	4		4		0
5	STAGE 2	3		3		0
6	STAGE 3	2		2		0
7	STAGE 4	2		2		0
8	STAGE 5	0		0		0
9	STAGE 6	3		3		0
10	STAGE 7	0		0		0
11	STAGE 8	0		0		0
12	STAGE 9	10		10		0
13	STAGE 10	1		1		0
14	STAGE 11	0		0		0
15	STAGE 12	0		0		0
16	STAGE 13	0		0		0
17	STAGE 14	0		0		0

Sheet: Sheet1

None of the values in columns C and E differ, and I have no understanding whatsoever of the need for column G, which is completely populated with zeroes. I am glad that somebody understands what you want, though.

[xenon325i]

Thanks again! Hopefully this is the last question now.... Because it does the copying on close is there any way to automatically answer yest to the dialog "Do you want to save changes...."

[xenon325i]

At the moment on the first day of running the all time best is what has happened today, so C & E are the same. Tomorrow though say STAGE1 made 6 and STAGE2 made 1. I would want column E, the all time best, to show 6 for STAGE1 (new new all-time high) but for STAGE2 column E would remain at 3 because todays output (1) does not break the all time high record.

[xenon325i]

I am still very confused by your sample data.

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G
3		Today		All-time Best
4	STAGE 1	4		4		0
5	STAGE 2	3		3		0
6	STAGE 3	2		2		0
7	STAGE 4	2		2		0
8	STAGE 5	0		0		0
9	STAGE 6	3		3		0
10	STAGE 7	0		0		0
11	STAGE 8	0		0		0
12	STAGE 9	10		10		0
13	STAGE 10	1		1		0
14	STAGE 11	0		0		0
15	STAGE 12	0		0		0
16	STAGE 13	0		0		0
17	STAGE 14	0		0		0

Sheet: Sheet1

None of the values in columns C and E differ, and I have no understanding whatsoever of the need for column G, which is completely populated with zeroes. I am glad that somebody understands what you want, though.

At the moment on the first day of running the all time best is what has happened today, so C & E are the same. Tomorrow though say STAGE1 made 6 and STAGE2 made 1. I would want column E, the all time best, to show 6 for STAGE1 (new new all-time high) but for STAGE2 column E would remain at 3 because todays output (1) does not break the all time high record.

[AliGW]

OK. I think you have your answer with the macro that has been provided, but for future reference, it is this level of detail that is required in a sample sheet so that we can see exactly what you are trying to achieve. Thanks for the explanation.

[xenon325i]

Thank you all for the help