Excel Help/ Question again :D

[nikaiyo2]

Thanks to you guys who actually know about these things, from my previous thread.

I have done a spread sheet so it takes prices from a worksheet, they are then used to generate a purchase order, on another sheet. I would like to be able to update the "price list" without the previous purchase orders updating...

Is there any way to stop a formula re-calculating?

[Harpoon]

You can set a spreadsheet not to automatically re-calculate formulas

https://www.automateexcel.com/how-to/stop-auto-cal...

[Doofus]

You can also copy the previous orders and "paste values" so they contain static numbers rather than formulae.

[LordHaveMurci]

Doofus said:

You can also copy the previous orders and "paste values" so they contain static numbers rather than formulae.

That was my thinking but I’m not an expert!

[Doofus]

LordHaveMurci said:

Doofus said:

You can also copy the previous orders and "paste values" so they contain static numbers rather than formulae.

That was my thinking but I’m not an expert!

The problem with switching on manual calculation is that when you eventually do the calculations, it'll update the old POs too.

I'd create a macro button on each PO sheet ("Finalise PO" or something) that turned the formulas into values.

[Mr Pointy]

Doofus said:

You can also copy the previous orders and "paste values" so they contain static numbers rather than formulae.

I've just discovered a keybpard shortcut for Paste Values - Menu key + V. It's great.

CTRL+V to copy
Menu + V to Paste Values

[nikaiyo2]

Thanks All

Paste values would work, but I want to hand this over to someone else once its working, so not great.

I think the manual calc option will be ideal

Thanks folks :P

[nikaiyo2]

Doofus said:

You can also copy the previous orders and "paste values" so they contain static numbers rather than formulae.

I want to be able to give this over to someone else to do, and dont really want them to have to copy and paste and get confused and make a mess lol

The Manual Calculation thing, (never knew that existed, so ) I think, will work a treat!

[Doofus]

nikaiyo2 said:

Doofus said:

You can also copy the previous orders and "paste values" so they contain static numbers rather than formulae.

I want to be able to give this over to someone else to do, and dont really want them to have to copy and paste and get confused and make a mess lol

The Manual Calculation thing, (never knew that existed, so ) I think, will work a treat!

The thing is that manual calculation will reclaculate those previous POs as well, unless you turn them into absolutes using paste values, and if you're going to do that, you don't need manual calc at all.

[mmm-five]

Are you using a simple lookup to return the prices? Are you in control of the values/format of the price list?

If so, can you extend the lookup to use both the item number and the date (even create a unique-id from the month+itemcode).

So each PO would only reference the item price from that month?

[nikaiyo2]

Doofus said:

The thing is that manual calculation will reclaculate those previous POs as well, unless you turn them into absolutes using paste values, and if you're going to do that, you don't need manual calc at all.

Ahh LOL, I was panning to effectively copy the sheet for each order, so create a new worksheet from the "master" that has Auto Calc enabled, then tell the other users once they have sent their PO to click manual calculation on the sheet relevant to that PO.

mmm-five said:

Are you using a simple lookup to return the prices? Are you in control of the values/format of the price list?

If so, can you extend the lookup to use both the item number and the date (even create a unique-id from the month+itemcode).

So each PO would only reference the item price from that month?

Yes in total control of it, what i do currently is update it as things change, so add a column and use this as the current price, I then keep the old prices dated the month they started, then use the effective date for the changes as the new date. I can get how to use lookup to match to a date, but can I match to partial date?

Yes =IFERROR(VLOOKUP(AE8,A:E,5,FALSE),0)

[Doofus]

nikaiyo2 said:

Doofus said:

The thing is that manual calculation will reclaculate those previous POs as well, unless you turn them into absolutes using paste values, and if you're going to do that, you don't need manual calc at all.

Ahh LOL, I was panning to effectively copy the sheet for each order, so create a new worksheet from the "master" that has Auto Calc enabled, then tell the other users once they have sent their PO to click manual calculation on the sheet relevant to that PO.

That way you have no way of stopping them accidentally or deliberately recalcuting the wrong sheet. I'd be locking down the numbers once a PO is created.

[imck]

I have something fairly similar

I have a list of items that looks up to another sheet that lists quantities in each warehouse
Sometimes a warehouse has no stock and is not on the lookup sheet.
I use Vlookup with Match to match the warehouse name

=IFERROR(VLOOKUP($A2,PriceList!$A$2:$T$1500,MATCH(I$1,PriceList!$A$1:$R$1,0),0),0)

Looks up stock code and returns the data in the relevant column on the pricelist for that stock code

Looks up the item in $A2 on Sheet1 to the Pricelist Sheet
$A$2 is where the data starts in Pricelist. $T$1500 is way beyond the Data but ensures if the data list grows it wont be outside the lookup area
I am matching the contents of I$1 on sheet1 with the Column Name in pricelist. This can also be text "August22" instead of the contents $I$1
$A$1 to $R$1 are the columns in pricelist

You would need something on your invoices to indicate the name of the column where the price comes from.

[paulrockliffe]

The 'correct' way to do this is to create your price list with a couple of extra columns, the start date and the end date, then your formulas reference both the date and the price to pull the correct price through.

If you're going to learn any new stuff for this, learn Power Query and do all the data modelling there, it's far more efficient than trying to model your data using formulas in Worksheet Cells.