Creating formulas & Macros

[FONT=Segoe UI, Tahoma, Arial, Verdana, sans-serif]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? [/FONT]

[FONT=Segoe UI, Tahoma, Arial, Verdana, sans-serif]Thank you.[/FONT]

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.

Thank you for your reply.

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

Try the attached file, and let me know if it suits.View attachment 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.

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.

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.View attachment Dynamic Hyperlinks example 20130619.xlsb

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

The Date Required box is cell I3. Change it and see what happens.

Your right it actually worked! Thanks.

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

Thank you, really appreciate all your help.

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/01/the-venerable-sumproduct.html
http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

Glad to have helped.

Hi Jeffrey,

Using the same principle we discussed earlier, I have noticed that I have 3 different types of booklets I want to select using this drop down.
The problem I might experience is that there are some that fall within the same date range, how I can use drop-down tabs to select the correct type and ensure I get the right pdf link for the type selected.

It is the same principle, I would like to know how I can make it work for three different type of booklet categories without getting the wrong file path. Is that possible?