Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Linking A Changing Reference (2000)

    Problem: On a daily basis, I enter various prices for inventory. Throughout the month, I do this daily and insert the prices , beginning on the first of the month, in column A, on the 2nd, column B, the 3rd, column C, etc.

    What I woulod like to be able to do is link this data to other files and pick up the most recent prices. For example, assume it is the 15th of the month, and I want to link to my prices spreadsheet and pick up the most recent prices (from the 14th). I would need a link to pick up the prices in column N. If it was the 20th of the month, I would need a link that would pick up the prices from column R.
    Thanks,
    Jeff

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    Something like this:

    =INDEX('Cata[test.xls]Sheet1'!$A$1:$H$1,4)

    Where your prices are in file Test.xls, sheet1, cells A1:H1 and in this case you want the fourth one (column D).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    Jan,
    Thanks for your quick reply. Let me make my example a little clearer-I'm not sure I understand your solution.
    Let's take today (the 2nd) for example. I have a list of 30 inventory products for which I received their cost as of the close of business yesterday (the 1st). I want to link those prices in cells A1:A30 to another worksheet automatically. Now tomorrow, I will update the prices, inserting them in colum B, cells B1:B30 (The reason I don't "overwrite column A is that I must keep all historical pricing on a daily basis), and if I want to update another file with today's close of business prices, I would want the "target" file to look to column B, cells B1:B30 for the update, etc., all the way to column AE (I think that is the 31st column).
    I hope this is clearer.
    Will your solution still work?
    Thanks,
    Jeff

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    My solution needs a bit of adapting then.

    If you add a cell to your pricing workbook that can count the number of columns that contain a price in an out-of-the-way position (lets say cell A50), containing this formula:
    =COUNTA(1:1)

    Then adjust the formula I have given you to read:

    =INDEX('Cata[test.xls]Sheet1'!$A$1:$H$1,'Cata[test.xls]Sheet1'!$A$50)

    This gives the last price on row 1. change the 1's to 2 to get the next row, etcetera (or remove the dollar sign before the 1 and copy down...).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    I'll try it-what does the formula COUNTA(1:1) do?
    Thanks,
    jeff

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    Counts all cells on row 1 that contain something other than nothing <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    Jan,
    Works perfectly. One refinement-At the heading of each column, I would like to insert the date of the prices, and allow that cell to be linked to other files as well. As a result, the date will "appear" at the top of the column only when there is data, say, in one or more of the cells in the same column, but not if there is no data there.
    Any ideas?
    Thanks,
    Jeff

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    I am not clear on what you need. Also, today is my last day before my holiday, so I am a bit short on time, sorry.

    Maybe someone else can jump in please?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    No problem-if no one else jumps in, I will contact you next Monday.
    Thanks, and Happy 4th!

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking A Changing Reference (2000)

    That would be next monday in August then...
    See my holiday announcement <smile>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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