Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Creating formulas & Macros

  1. #1

    Creating formulas & Macros



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I'm looking for some assistance with excel. I have been attempting to set up the following but have had no luck: Basically, 'in the first box I would to type a start date 'Second box should find or show the available info for that date range (the range of dates and info will be available to use for formulas 'Third box should then generate a 'a link or file path for the correct set range of data I have listed' Now, I need some assistance as to how to set this up on excel, any ideas?

    Thank you.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Yep, this can be accomplished using the CHOOSE function and cascading dropdowns. If you whip up a sample dataset and post it here, I'll retrofit a solution to it for you.

  3. #3
    Thank you for your reply.

    I have attached an excel doc as a sample, hope it helps.
    Attached Files Attached Files

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Try the attached file, and let me know if it suits.Dynamic Hyperlinks example 20130619.xlsb

    Note that I'm using Excel Tables (new in Excel 2007) and data validation to do this.

    The good thing about tables is that you can add a new line to your lookup table, and the dropdown list will include the new item automatically.

  5. #5
    Hey thanks for your efforts.

    I think we are nearly there, however in the 'date required' filed I would like to be able to manually type a date within that range and be able to generate the name of the file path and the PDF link.

    My second question then, is when we have established this fomula I would like to make the table invisible, but we can discuss this after we have overcome the above.

    Much appreciated.

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Okay, try this file. You can cut the lookup table and paste it onto another worksheet, then hide that worksheet so users can't see it.Dynamic Hyperlinks example 20130619.xlsb

  7. #7
    Thank you very much, we are very close. Only thing that is missing is in the 'date required' box, as it should generate the 'name of file path' and 'pdf link' when a date is entered in its range but this doesn't. If we can amend that to generate those links as soon as we enter the date then it will work fine.

    I think everything else is perfect.

    I would really like to learn how you did this..would be very useful fo me.

    Thanks

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    The Date Required box is cell I3. Change it and see what happens.

  9. #9
    Your right it actually worked! Thanks.

    So, how were you able to do that..whats the formula?

    Thank you, really appreciate all your help.

  10. #10
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    THe formula is:
    =IFERROR(HYPERLINK(INDEX(Table1[pdf link],SUMPRODUCT((I3>=Table1[Date Start])*(I3<Table1[Date End])*Table1[Position]))),"Outside Date Range")

    You can't click directly on the cell, because the hyperlink will kick in. But if you select a cell near it and then use the arrow keys to take you to I3 then you can see it in the formula bar.

    THe important bit is the SUMPRODUCT bit:
    SUMPRODUCT((I3>=Table1[Date Start])*(I3<Table1[Date End])*Table1[Position])))

    And also note that I'm using Excel Tables, which have quite different referencing styles than normal ranges e.g. Table1[Date Start] tells Excel that I want the entire 'Date Start' column from Table1

    There's some good resources on SUMPRODUCT here:
    http://www.excelhero.com/blog/2010/0...umproduct.html
    http://chandoo.org/wp/2009/11/10/exc...oduct-formula/

    Glad to have helped.

Page 1 of 2 1 2 LastLast

Posting Permissions

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