Excel 2003 Formula which moves on week by week, linked to another worksheet

Hi,

Can anybody Help ?

I have an excel Workbook with a summary sheet and a number of other worksheets. The worksheet compares product pricing with the previous week on a weekly basis over 52 weeks. at the end of the table I have a formula which brings back one of the following: No Price, NS (Not Seen), The current week's price, or the difference if it has changed, the formula is:
=IF(D31="No Price","No Price",IF(D31="NS","NS",IF(OR(C31={"No Price","NS"}),D31,IF(SUM(D31-C31)=0,D31,SUM(D31-C31))))).

However on the summary sheet, I want to bring back the result to the corresponding cell (list of products) - BUT I want to move it on each week, i.e. so the summary sheet just shows the latest week compared to the previous week in the same cell for each product. I can just copy the above formula across on the worksheets so it looks at each week v previous weeks but not sure about the summary sheet...& moving the formula on each week ?

Massive thanks in advance,

Dave

Hi David and welcome to the forum,

I believe we need a sample workbook with about 3 sheets to help you much on this problem. At first glance you need a vlookup of come kind on the summary sheet instead of an exact cell reference on the sheet before.

I need to see the layout of your data and how your formulas deal with the sheet before and what you are trying to achieve on the summary.

To attach a sample workbook, click on the Go Advanced below the message area and then on the Paper Clip Icon above the message area to open the Attachment Dialog box.

Originally Posted by DavidMarston

Database error
The Excel Help Forum database has encountered a problem.
Please try the following:
Load the page again by clicking the Refresh button in your web browser.
Open the www.excelforum.com home page, then try to open another page.
Click the Back button to try another link.
The www.excelforum.com forum technical staff have been notified of the error, though you may contact them if the problem persists.

We apologise for any inconvenience.

Oh-no...not again, hopefully this is just an isolated case.

Please change your title according to Rule 1 of this forum's rules.

Then trying slimming down the workbook or zipping it before uploading.

Hi David,

When you say changes to price, is that from week 1 to week 2 on the save sheet, or is it a change in price from Morisson to Tesco on week 1?

This is exactly why I needed to see an example. I thought you week 1 as a different sheet from week 2.

I think I have a way. VLookup should work if you create a unique index. Give me a few minutes to show you

Ok David,

Here is the plan. You need to have a unique name for each of your products in Column A on each sheet, including and agreeing with the Retail Changes sheet.

I appended a S M or L on each Cheese name in Col A that had Small Med or Large in col B by doing this.
I inserted Col A and then used this formula in A4 and down for each sheet.

Please Login or Register  to view this content.

Then I had to copy Values Only back over your original names and delete Col A.

Do the same on the Retail Changes Sheet to get unique names in Col A.

Then in cell G4 of the Retail Changes sheet put this formula

Please Login or Register  to view this content.

This will subtract week 1 price from the standard (assumed) hidden price. You can add to this formula to not show zeros so only changed prices display.

Pull this formula down for all of Col G.

Where you have #NA on the Retail Changes sheet you have non matching names of cheeses. Fix the spelling or spacing or names on the Retail Sheet to be EXACTLY like those on the other sheets and things should work.

Hope this helps.

Hi David,

Instead of using 4 and 5 in the VLookup formula substitute it with Column() and Column() + 1 . I'm not sure if that is exact as it might have to be Column() +3 and Column() + 4.

It just occured to me, based on your worksheet design this may not work.

How about this. In cell B1 on the Retail Changes sheet put the number 4 and then in the VLookup formula make it.

Please Login or Register  to view this content.

Then when you want the next week simply change the number in B1 and all the formulas will be correct.

hope this makes sense.