Creating formulas & Macros

salgunaidi

New member
Joined
Jun 18, 2013
Messages
7
Reaction score
0
Points
0
[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]
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 

salgunaidi

New member
Joined
Jun 18, 2013
Messages
7
Reaction score
0
Points
0
Thank you for your reply.

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

Attachments

  • policy booklet calculator example.xlsx
    10.9 KB · Views: 12

salgunaidi

New member
Joined
Jun 18, 2013
Messages
7
Reaction score
0
Points
0
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.
 

salgunaidi

New member
Joined
Jun 18, 2013
Messages
7
Reaction score
0
Points
0
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
 

salgunaidi

New member
Joined
Jun 18, 2013
Messages
7
Reaction score
0
Points
0
Your right it actually worked! Thanks.

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

Thank you, really appreciate all your help.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 

salgunaidi

New member
Joined
Jun 18, 2013
Messages
7
Reaction score
0
Points
0
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?
 
Top