Reference a cell within a HyperLink

Hi,

I am trying to create a formula which will auto populate hyperlinks in excel based on preexisting data in my spreadsheet. I am storing sales orders named after their corresponding sales order # in folders named after the dates they were created I.e (=HYPERLINK("C:\Users\????\Desktop\Packing Slips\2018-03-02\"&B7&".pdf". I have managed to create a formula to automatically find the sales order number however for whatever reason I am having a problem referencing the date portion in cell B7 with the hyperlink. Your help would be greatly appreciated. My formula currently is =HYPERLINK("C:\Users\????\Desktop\Packing Slips\2018-03-02\"&B7&".pdf",CONCATENATE(TEXT(B7,"mm-dd-yyyy "),A7)).

Thanks.

Originally Posted by kev_

You have not told us what the file name is. "Friendly name" in the formula suggest that the file name is a concatenation of "A7" and "date in B7"
BUT A7 does not appear in your hyperlink string (only in the friendly name string) so I am confused
`
Is this the correct hyperlink string for you ? "C:\Users\????\Desktop\Packing Slips\2018-03-02\"&A7&TEXT(B7,"mm-dd-yyyy")&".pdf"
With A7= FileXXX , and the date 8 March 2018 in B7 it returns C:\Users\????\Desktop\Packing Slips\2018-03-02\FileXXX03-08-2018.pdf

What you need to do
1. Enter this formula in any cell

Formula:

Please Login or Register  to view this content.

2. Amend it until the cell returns the correct full path and file name -be very careful with spaces - everything must be identical
3. Use in HYPERLINK formula

If you are still struggling after that, then you need to tell us the EXACT file name, the value A7 and the value in B7

Hi,

Unfortunately, I am still struggling, I am not the best with formulas like these so it could be something simple. The A7 portion of my link works on its own but its the B7 portion which needs to be inserted in place of the date in the link that is causing problems. Anyways, the file name is 24103.pdf and it is located in a folder called 2018-03-02. The value of A7 = 24103 and the value of B7 is 2018-03-02 (or "43161" with a date format applied). I believe I need to tell the formula to convert the date (B7) from its number (43161) to the format of my folder (2018-03-02) but I cannot finish the formula as there is an error.

File Name = 24103.pdf
A7= 24103
B7 = 43161 (displayed as 2018-03-02 due to date formatting))

Originally Posted by kev_

If your file name is in A7....why are you telling Excel to use the value in B7?
your original post :

Formula:

Please Login or Register  to view this content.

Replace with A7 and it should work (if you also replace ???? with rest of filepath )

The reference of B7 is important. Basically, I have a report which I export from another program into my spreadsheet each day which adds new "sales orders" for the current day into column A and the date they were created in column B (current day). The corresponding Sales order pdf's are then filed away and renamed after their order # each day in a folder which is named after the current date on my desktop (as the file path shows). So what I am trying to do is create a hyperlink in Excel which I can place in column c and drag to automatically generate links for the corresponding sales order in Column A. The formula has to reference column b (date) in order to change the folder where the PDF's are stored as they are filed by date. If I ignore the date portion in column b than I would have to type in the date each day before dragging. It's not a big deal but its not ideal. I have actually done a similar formula in the past and I can't remember what I did but I know it is possible and it saved me a ton of time.

Originally Posted by kev_

Ok final attempt
- all this is based on what you have said

File Name
24103.pdf

A7 value
24103

Your folder
"C:\Users\????\Desktop\Packing Slips\2018-03-02\"

So full path to file is
"C:\Users\????\Desktop\Packing Slips\2018-03-02\" & A7 & ".pdf"
which gives this
"C:\Users\????\Desktop\Packing Slips\2018-03-02\24103.pdf"


If that is not the correct full path to the file what should it be?
(type it out for me without referring to cells - I need the path and file name - that is what Excel needs)

That file name is correct and the link does work. However, I still need the date in B7 to be referenced for the formula to do what I am trying to do. But yes, that formula on its own works and the file path is correct.

Originally Posted by kev_

Is it this perhaps
"C:\Users\????\Desktop\Packing Slips\" & Text(B7,"YYYY-MM-DD") & "\" & A7 & ".pdf"

Perfect. Thank you very Much!