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

    Summary Page (2000)

    Relatively easy (I think!): I have an excel workbook that contains 12 worksheets, each corresponding to the months of the year. On each worksheet, I track, by day, prices for certain commodities. Specifically, column A contains the name of the commodity (in this case different months of natural gas futures (i. e., June 03, July 03, etc., all the way out to May 08)), and columns B-AD or -AE, depending on whether the month has 30 or 31 days (-AB for February, etc.), the prices for each month on each day of the respective month the future is traded. For example, in column B, I would show the prices for each natural gas futures contract that was listed in column A that occurred on the first day of ther month; in column C, the prices that were recordced on the 2nd of the month, and so on, all the way out to the last day of the month. I manually enter the prices from an external source each day.

    My problem is that each of the worksheets is named for the month it has data for (i. e., "June 03", etc.), and I have many, many other excel files that need the most current futures contract price. If I set up a link, say to the current worksheet, May 03, then if I open a file in June that has the May link, I won't be getting the most current prices. Does anyone know of a way I could set up a summary page that would "extrapolate" the most recent or cuerrent price from the various monthly sheets?
    Thanks in advance,
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary Page (2000)

    You can use the INDIRECT function to create a dynamic reference. It could be done in one formula, but I'll break it down into steps so that you can see how it works.

    In the workbook that should be linked to your commodity workbook, enter the name of the commodity workbook into a cell, say A1.
    Enter the formula =TEXT(TODAY(),"mmm yy") into another cell, say A2.
    Enter the adress or name of a cell you want to link to into a third cell, say A3.
    Enter the formula<pre>"'["&A1&"]"&A2&"'!"&A3</pre>

    into a fourth cell, say A4.
    Finally, enter the formula =INDIRECT(A4) into the cell where you want the link.

    Note: the commodity workbook has to be open for this; INDIRECT doesn't work if the workbook referred to is closed.

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

    Re: Summary Page (2000)

    Hans,
    Thanks for your quick response; however, I don't seem to understand. First, assume the name of the commodity workbook is NYMEX.xls, and it is on a shared"U" drive, withn the address "U:SharedNYMEX.xls". What name should I enter in A1: "U:SharedNYMEX.xls" or what? In addition, in your step three (3), you say enter the address of the cell you want to link to. That's my problem, as I update the commodity workbook each day, the most recent prices change cell positions. For example, column B would contain all of the prices on May 1 in the May worksheet in the commodity workboor; C, the prices on May 2nd, etc. When June arrives, I create a new worksheet with the same format. Hence, a link to the most recent price today will not be a valid link (for purposes of retrieving the most current prices) say in a week, or a month, etc.
    Sorry i wasn't clear before.
    Jeff

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary Page (2000)

    1. As I wrote in my previous reply, you should enter the workbook name in cell A1 in my example. In your case, that is NYMEX.xls. You don't have to specify the path because NYMEX.xls will have to be open for the INDIRECT formulas to work.

    2. I described a way to refer to the worksheet for the current month; you can extend this to refer to the column within that worksheet that corresponds to the current day. Alternatively, you could use dynamic range names to refer to the last column with data within a workbook. Do a search if you want to learn about dynamic range names.
    If you want to refer to the most recent worksheet with data if that is not necessarily the current month, that would be more difficult. Offhand, I can't think of an easy way to do that (without VBA).

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

    Re: Summary Page (2000)

    What I am looking for is maybe a summary page in the commodity workbook that would list all of the monthly contracts I am following during the year and the most current price of each. As such, the formulae on the summary sheet would "search" through all of the monthly worksheets and find the most recent date a price was entered for each month of a contract. I like your idea about a dynamic price, but I don't think it does what I am looking for.
    Any other thoughts?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary Page (2000)

    What you want would probably be easier if you organized your data in a database-like format, in a table in one worksheet with all dates below each other, more or less like what Steve (sdckapr) proposes in <post#=249850>post 249850</post#> in a thread started by someone else. It would mean a complete refurbishing of your spreadsheet and all spreadsheets depending on it, though.

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

    Re: Summary Page (2000)

    Hans,
    See my post above entitled "last data entered accross 2 worksheets" that is a variation of the current thread. My last post to Steve, and by this post I ask you as well, is how can the formula be written to include 12 worksheets, not just 2?
    Thanks,
    jeff

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary Page (2000)

    I can only repeat my previous reply. I think your present structure is making things very difficult.

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

    Re: Summary Page (2000)

    Thanks for yopur help anyway,
    Jeff

Posting Permissions

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