Changing LOOKUP table prices changes previous sales too!

Excel 2007

I have a sheet called SALES RECORDS and another one called PRODUCT LOOKUP. I use a table in PRODUCT LOOKUP to return product costs when I enter a sale in SALES RECORDS. It's simple, but it works fine. Except.

Today I changed the cost of a product, and that cost changed was reflected on ALL previous sales. Is there a way to change the data in product costs so that the costs for previously entered sales will not change?

TIA,
Cane

Originally Posted by K m

There are a few ways to do this.

- One way is to change the cell references to a cell with the new price. This could easily be done with FIND/REPLACE
The FIND/REPLACE would be for new prices only. Leave the other formulas alone

- A second way is to "lock in" the previous prices. Copy a range of cells and PASTE/SPECIAL/VALUES.
Those cells would no longer have formulas but have "fixed" numbers

- The last approach is to make a duplicate copy of the worksheet.
This new worksheet could have different formulas to reflect the new prices.

No. 1 - I should have thought of that one...it would have done the trick but your 2nd suggestion looks like the better long term one.

No. 2 - I didn't know that could be done...although I could have researched that before I asked. This is a good suggestion AND something I can use to solve another unrelated problem I am having.

No. 3 - If I understand that one correctly I would be creating new spreadsheets each time I have price changes.

Thank you very, very much for your time and attention, K m - this will do the trick for me.

Cane

Originally Posted by FDibbins

to add in to K M's 2nd suggestion, you could add a date to all of you'r prices in a 2nd column, and then use that to help seperate btw the old and updated price lists. add a helper column and concaternate (using =(date-cell&date-cell), and then do the vlookup based on a concatenate on your data sheet into the helper column.

if this is a little confusing to you, upload a sample workbook, and i will help you get started

This looks like REAL WORK, but it's probably in my best interest to LEARN this and I think this will save me problems in the long run.

Yes, it is a little confusing, but I will work on it myself 1st. I don't want the reputation of someone who just wants things done FOR ME. Learning Excel is part of the goal.

IF I can't get it with a reasonable amount of effort on my part, I will take you up on your very generous offer!

Cane

---------- Post added at 09:31 AM ---------- Previous post was at 09:23 AM ----------

Ah...yes...my mistake...sorry...wrapping my mind around the WORKBOOK, WORKSHEET, SHEET, etc., terminology has been harder for me than some of the functions. Several weeks ago all I knew was SPREADSHEET.

Ah...yes...my mistake...sorry...wrapping my mind around the WORKBOOK, WORKSHEET, SHEET, etc., terminology has been harder for me than some of the functions. Several weeks ago all I knew was SPREADSHEET.

---------- Post added at 09:48 AM ---------- Previous post was at 09:40 AM ----------

Originally Posted by K m

I thought of another approach that you may consider:

- You might include a Lookup table with DATES and PRICE
- You would have to provide a column or range to indicate the date.
- Write a simple Lookup formula to lookup the price based on the date you supplied
- That way the approaches above would not be necessary and all of the formulas could be the same

This approach would be more difficult to set up but would be easier to follow once it is set up

Kirk

This sounds really good...I think I can do it...

Just so I am clear...I have all the formulas copied presently to row 5000. Today I would redo the formulas and recopy them (only from today's 1st row) to row 5000. WHEN I have another price change, I reset the formula accordingly and recopy to row 5000 from THAT day's 1st row.

Am I close???

The formula would like this:

=Vlookup(A1,Price_Array, 2, False)

where: A1= lookup_date {change accordingly}

Price_Array = Array of Dates and Prices {I would define a named range}

2 = col_index, {In this case, values are the 2nd column}

False = {Dates and prices do not have to be in order}

Please see attached. If the cost of Cats goes up to $2.00(and in this economic climate that's a real possibility)I change B2 in ProductLookup to reflect that for future sales. However that will chance my cost for the Cat I sold on 6/12/12.

My sense of it that identifying the change by date will cause a new row to be created.

Maybe it would be simpler to us PASTE/SPECIAL/VALUES occasionally.

Thank you ever so much for this help.

Cane

Originally Posted by K m

This is what I had in mind

=IF(C2="",0,INDEX(ProductLookup!$B$3:$G$11,MATCH(C2,ProductLookup!$A$3:$A$11),MATCH(B2,ProductLookup!$B$1:$G$1)))

The First MATCH uses the SKU to find the proper row in the ProductLookup
The Second MATCH looks to see at the DATE and matches the proper column in the ProductLookup. Therefore if there has been a price increase before the ship date, the new price is reflected.

I am sending you your file back with the equations


I hope I explained it well enough

You're too kind, K m - thank you. I really don't expect people who help me to "do it for me", but I realize it could take a lot more of your effort and time to teach me.

And, I absolutely had the wrong thing in mind. Had you helped me do what I was trying to do, you'd have helped me make a mess.

I understand this well enough to use it now, and probably to make future use of it. This is going to be a heck of a tool for me.

I just can't thank you enough...

Best regards,
Cane

---------- Post added at 07:07 AM ---------- Previous post was at 06:55 AM ----------

K m -

Cane Rivero here - again.

I'm interested in the idea of "lock in" previous prices.

As I move my cursor around the spreadsheet, sometimes unintended things happen. 1st, I have to learn how to move the cursor around without having that happen, and I will. But...

...can you suggest a sort of "best practice" ?

"Lock in" say, at the end of each week, would (I think) do it...but since I know so little maybe you could tell me if there is a more appropriate way of keeping things from changing?

Regards,
Cane

Take the spreadsheet you just helped me with. I could accidentally change something in ProductLookup and not even know it changed a lot of things in Sales.

Locking in, as described in your previous message, sounds like the thing to do. But, since I was coming at the other problem from the wrong direction, I thought I would ask you if there are standard procedures that experienced Excel programmers use to prevent inadvertent errors.

Thank you,
Cane

Good point...and I'm trying...hehehehe.

But, as to the main sheet (rather than the price lookup sheet), any disadvantage to locking down what I don't ever want changed?

Many years ago a popular American television host was interviewing a Hispanic man who spoke English very well but did make occasional small errors. The host, a comedian, made fun of one error in particular causing the Hispanic man to remark: "Remember, Johnny, I know one more language than you do."

Thank you,
Cane