+ Reply to Thread
Results 1 to 2 of 2

Pricing for Inventory

  1. #1
    Registered User
    Join Date
    01-09-2004
    Location
    Palm Bay, Fl.
    Posts
    13

    Question Pricing for Inventory

    Hi,
    This is my formula now.

    =VLOOKUP(B6,Prices!A3:AB68,3,FALSE)

    What I want to do is have the above formula look at a date, say is in cell B5 and pull the price that corresponds with that date. The B6 in the formula represents the state. Let’s say the date is 08/01. My date on this invoice is 08/10. I want the prices from 08/01 to be inserted. But let’s say there is a price change 09/01 and the date of my invoice is 10/15, I want the price pulled from the 09/01 date. Putting an actual date in the formula won’t work because it will be constantly changing. I could be working ahead and using the 10/15 date in Aug. so it needs to pull the correct prices. Is there a way to do this?? If not, is this something I maybe can do in Access? Thanks.

  2. #2
    Toppers
    Guest

    RE: Pricing for Inventory

    I believe the only solution is to use a combined "key" of date & state in
    your VLOOKUP table. Table would need be sorted ascending by date.

    =VLOOKUP(C1&D1,Prices!A1:AB100,2,1)

    C1=Date
    D1=State

    Prices!A would contain concatenated key.

    "edrachel" wrote:

    >
    > Hi,
    > This is my formula now.
    >
    > =VLOOKUP(B6,Prices!A3:AB68,3,FALSE)
    >
    > What I want to do is have the above formula look at a date, say is in
    > cell B5 and pull the price that corresponds with that date. The B6 in
    > the formula represents the state. Let’s say the date is 08/01. My
    > date on this invoice is 08/10. I want the prices from 08/01 to be
    > inserted. But let’s say there is a price change 09/01 and the date of
    > my invoice is 10/15, I want the price pulled from the 09/01 date.
    > Putting an actual date in the formula won’t work because it will be
    > constantly changing. I could be working ahead and using the 10/15 date
    > in Aug. so it needs to pull the correct prices. Is there a way to do
    > this?? If not, is this something I maybe can do in Access? Thanks.
    >
    >
    > --
    > edrachel
    > ------------------------------------------------------------------------
    > edrachel's Profile: http://www.excelforum.com/member.php...fo&userid=4736
    > View this thread: http://www.excelforum.com/showthread...hreadid=552287
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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