+ Reply to Thread
Results 1 to 9 of 9

Breaking Dynamic list prices

  1. #1
    Registered User
    Join Date
    10-04-2017
    Location
    sri lanka
    MS-Off Ver
    10
    Posts
    7

    Breaking Dynamic list prices

    Hi all
    I'm sure I'm missing something obvious but can't seem to find an answer on the forum for this.

    The Background:
    I have a recipe and inventory workbook for a restaurant with inventory pricing and recipe pricing
    I'm also adding in inventory usage and stock monitoring over time. The issue comes in when stock prices for individual items [change

    For example:
    I do a stock count on Monday and the system pulls all the prices from the inventory sheet and calculates the value.
    during the week the accountant adds purchases and updates the current price of every item
    On Friday when we count again, the price is pulled from the inventory and the current stock value is recorded.

    The Problem: as we change the inventory pricing, the previous stock values (ie Monday) also change as the pricing is dynamic

    What i need:
    I'm looking for the best way to set this up that is smooth and idiot proof and doesn't really involve the accountant copying and pasting 'as values' as if he does this wrong once and I miss it, I can see it messing everything up.

    So how do I ensure that stock value at a given date is 'frozen' for that date so that future values can be compared correctly?

    Thanks for any input.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Breaking Dynamic list prices

    please upload your file.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,387

    Re: Breaking Dynamic list prices

    Welcome to the forum!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    10-04-2017
    Location
    sri lanka
    MS-Off Ver
    10
    Posts
    7

    Re: Breaking Dynamic list prices

    Hi Guys

    Thanks for taking the time and apologise for not attaching the file earlier. I had to remove most of the recipes to get under the size l;limit so ignore the REF errors on the inventory sheet
    hope this makes sense, my next challenge is the best way to record stock item usage from sales but thats probably for another post!
    Attached Files Attached Files

  5. #5
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Breaking Dynamic list prices

    if there were all purchase dates in your inventory it'd be easy to assign prices

  6. #6
    Registered User
    Join Date
    10-04-2017
    Location
    sri lanka
    MS-Off Ver
    10
    Posts
    7

    Re: Breaking Dynamic list prices

    Agreed, my first thought was the same, however, I'm unsure how to update pricing for the recipe tabs (the original file has hundreds) if there is a rolling set of prices in the inventory

    I do love the idea of recording historical pricing as it allows me to automate price alerts.

    is there a simple and foolproof way to have price fields in the recipe tabs update to latest pricing so that we can use historical pricing for counts?

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Breaking Dynamic list prices

    3 main sheets:
    1 inventory with dates as excel table
    2 sales with dates as excel table
    3 calculation for specific dates

  8. #8
    Registered User
    Join Date
    10-04-2017
    Location
    sri lanka
    MS-Off Ver
    10
    Posts
    7
    Right I can see that setup working but then pricing on the several hundred recipe sheets will not update.

    The issue is to have historical prices fixed but have the recipe coatings dynamic.
    Last edited by AliGW; 11-12-2018 at 01:55 AM.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Breaking Dynamic list prices

    See if the following is of any help and if so I will provide further explanation.
    The price of espresso coffee on the inventory sheet is displayed using: =LOOKUP(99^99,'Good Recieved'!D20:DC20)
    The price r/u of espresso coffee on the spot count 29 10 18, stock count 29 10 18* and spot count 5 11 18* sheets is displayed using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Varies slightly
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to sum prices in a list for different buyers
    By JamesR12 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-17-2017, 07:08 AM
  2. Replies: 0
    Last Post: 04-14-2016, 12:44 AM
  3. [SOLVED] Extracting latest prices from a price book with past and present prices
    By benoj2005 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-10-2012, 04:08 AM
  4. Breaking up a long list into different worksheets
    By blurtoad in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2007, 06:29 AM
  5. [SOLVED] Look up a list of prices
    By Numpty in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-06-2005, 09:00 PM
  6. Use a multiplier to change List Prices to Net prices
    By Dangada in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 12:05 PM
  7. set up prices for a drop down list?
    By fireycowgrl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1