Help with formula needing "placeholders" for potential hyperlinks....

Hi,

I have a simple concept, but I'm sure it requires a complex string of functions/coding. So here's what I am trying to do:

Let's say four columns: A, B, C, D

Column A will be used for hyperlinks added at any given time

Column B will reference and copy text located in a specific cell within the hyperlink provided in column A

Column C will reference and copy a date located in a specific cell within the hyperlink provided in column A

Column D will reference and provide the average of five specific cells within the hyperlink provided in column A

Functions in B1, C1, and D1 should reference hyperlink in A1; B2, C2, and D2 reference hyperlink provided in A2, and so on.

What I need is a function or functions that will leave columns B, C, and D empty until a hyperlink is provided in A, and then they carry out their respective functions referencing the specified cells within the hyperlink that gets put into column A. The only way I can think to describe this in non-technical terms is having placeholders in an IF function that get replaced by whatever hyperlink gets entered into the A column (where "x" equals the contents of column A: B1=IF(x=,,=SUM([column A hyperlink]Sheet1!B14)) ). I know that that isn't a proper formula, but hopefully it helps illustrate what I am trying to do. I want the formulas to be present at all times so that it does the work automatically when a user enters a hyperlink into column A (rather than having to write the functions for the respective cells each time a hyperlink is inserted). I know how to logically form a formula string, but I'm not familiar enough with Excel or the functions within the program to write this formula myself (if it's even possible to do so). I've been looking at the TEXT functions REPLACE and SUBSTITUTE, but I don't know if those have anything to do with what I would like to do. If anyone is able to provide some help, I will be eternally grateful; I have the will, I just need help finding the way! Thank you!

Dustin

Crooza,

The problem I'm faced with is the formula that will be used if the IF statement returns false. Let's say it's the SUM formula: I want it to use whatever hyperlink gets entered into the A column. When writing the formula, it needs a finite document to reference, such as [JohnSmith.xlsx], but that hyperlink won't be present in the A column until the user enters it, so the formula won't be complete at the time it is written, hence the need for a "placeholder" in the formula that will be replaced once a hyperlink is entered in column A. And, I'm probably not explaining this correctly or clearly enough, so I do apologize.

OK, I'll give it a try.

A1 is empty, therefore B1 is empty. (B1=IF(A1="","",=SUM([PLACEHOLDER.xlsx]Sheet1!B14)))

I add a hyperlink in A1 that links to document JohnSmith.xlsx, so B1 returns the text listed in B14 of the document JohnSmith.xlsx (automatic change from =SUM([PLACEHOLDER.xlsx] to =SUM([JohnSmith.xlsx], which is the hyperlink entered into A1.)

Make a little more sense?

Returns an invalid cell reference error.

I copied and pasted the formula you posted on here with both &.

It's close though. It does insert the file into the formula, it just shows a reference error after that.

I just tried with both files open, and with the file being referenced closed; neither work. One file is located on my desktop and the other in another file elsewhere. While I'm waiting for your response, I will move the files to the desktop to see if that makes a difference.

Ok, now that both files are located within the same directory, and both are open, it works. Now, if we can figure out how to make it work if I have a Master Folder that contains a folder for Rosters (the documents that will reference the hyperlinks) and a folder of Associates (the documents being referenced) without having to have the referenced documents open, that would be awesome. So far you are on a roll, so you definitely will be getting some street cred from me when we're done.

I appreciate it. You have been very helpful thus far, and your time means a lot, so thank you! I will be signing off for the evening, but I will respond to any replies tomorrow. I think between the two of us, we've explained enough for someone to work on the problem with minimal input. If someone out there knows how to do this, I think it could be very useful to many users out there. Have a great day over there!

Sixthsense,

Fantastic! This is exactly what I am looking for. Now, one other thing that I need in conjunction with this function: I want to use the indirect.ext function to access cells D19:D23 and show the average of the values within those cells. So far, using an IF function, my formula looks like this =IF(A1="","",=INDIRECT.EXT("'C:\Users\*****\Desktop\MasterFolder\Associates\["&A1&"]Sheet1'!"B14)) which returns whatever value is in B14 of the referenced document, obviously. Now how\where do I add the AVERAGE function in that formula to find the average of cells D19:D23?

Nevermind. Thought about it literally right after posting: it needs to be =IF(A1""."",AVERAGE(INDIRECT.EXT... for anyone referencing this for their own use. The function you want to perform comes right before the INDIRECT.EXT function. So problem solved! DONE AND DONE! Crooza you rock, Sixthsense you roll, and together you two Rock n' Roll; corny, yes, but you two are still lifesavers, so thank you very much! I'm learning a lot of this on my own, so hopefully one day soon, I will be able to do for others what you've done for me. Pay it forward, right? Take care!