how to make #REF dynamic in excel

February 12, 2011 at 09:03:17
Specs: Windows Vista
Hi, for my sheet, I have references to another sheet that it pulls data from and populates a particular cell, I have created the sheet with nearly 200 #REF links like this. The problem is the location of the sheet changes, the reference looks like this currently:


I am however looking to make that link dynamic so it will pull up the t.htm and get the info from that file regardless of its location (as long as it is open and active in excel)

Right now I must go to the "Security Warning Automatic update of links has been disabled" and change "Source". Is there a way that this can automatically change the links? I really just need it to be something like:


because t.htm is currently opened in excel.

thanks in advance.

See More: how to make #REF dynamic in excel

February 12, 2011 at 12:08:04
I'm not sure what you mean by "I have created the sheet with nearly 200 #REF links".

#REF is an error and I doubt anyone would intentionally create 200 errors.

In addition,=t.htm!$I$7 doesn't make sense because, based on your original formula, t.htm appears to be a file name, but when used in =t.htm!$I$7 t.htm represents a sheet name.

I'm pretty confused at this point.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 12, 2011 at 15:25:04
The sheet I created has references to another sheet called t.htm. That sheet changes daily, It is a web page. I save the page and name it the same thing (t.htm) each day. The only other variable besides the data on the page is that the page is saved into a folder for that day, example the folder name would be 2-12-11 and then inside that folder you would find the .htm files and so forth for t.htm

I then open the t.htm in excel and the information I need always starts at the same place (row / column)

I then open another sheet lets call it callout, a form if you will, and this sheet has references to the t.htm file that I just opened. it pulls info and populates the cells of the callout sheet from the info thats on the t.htm sheet.

The problem is the references on the callout sheet look like this:


The problem is that the folder or location of C:\Users\Acomputer\Desktop\test\02-15-10\1\ will change daily. Thus, causing me to "change source" by editing the links on the page. I was hoping to find a way that the callout sheet would automatically pick up the links from whatever t.htm file I had opened already.

I can give screen shots if it would help show what I am saying, I am sure that my explanation is horrible. thanks

Report •

February 12, 2011 at 17:09:11
Your interchanging of the terms "sheet" and "file" is what makes your question confusing.

t.htm is not a sheet, it's a workbook.

callout is not a sheet, it's a workbook.

You don't save or open a sheet, you save and open a workbook. That workbook will contain 1 or more sheets.

When you use this formula:


the t.htm is the file name, the t is the sheet name,

When you said "I really just need it to be something like =t.htm!$I$7 you are asking for a formula that references a sheet named t.htm, not the file t.htm.

OK, now that we've cleared that up, if I understand your issue, it's this:

If you hard code the folder name into the formula (e.g. 02-15-10) the formula will link to "old data". You'd like the formula to reference a different folder name - i.e. the "newest" one - at any given time.

I can think of 2 possible solutions.

Option 1:

Save the latest file twice: Once into a folder for that day and once into a folder called e.g. "Most Recent". Then you could just reference the file in the Most Recent folder for your latest data.

='C:\Users\Acomputer\Desktop\test\Most Recent\1\t_files\[t.htm]t'!$I$7

This would not have to be a manual operation. Back in the days of floppies, I used have a macro that I called "Dual_Save". When I clicked the button, the file would get saved into 2 locations: The client's folder on my hard drive and then onto a floppy so I could give it to my clients. The code would pop up the standard Save As dialog box so that I could create a new folder for each client on my hard drive, then the file would get saved to that folder and then (automatically) to the floppy. In your case, instead of saving it to a floppy, your second save would go to the folder called Most Recent.

Option 2:

Consider the use of the INDIRECT function.

The INDIRECT function uses a text string to build a reference to a cell. You can include both text (within quotes) and cell references, the results of a formula, etc. to "build" the reference.

For example, all of these will reference Sheet2!A4:


=INDIRECT("SHEET2!A" & A2) (assuming A2 contains 4 or a formula that evaluates to 4)

=INDIRECT("SHEET" & IF(10>5, 2, 3) & "!A4") (since the IF function evaluates to 2)

So, how does this help you?

Well, if you could use a cell someplace that holds a value that represents the folder name that keeps changing, your link formulas might look something like this:

=INDIRECT("'C:\Users\Acomputer\Desktop\test\" & $A$1 & "\1\t_files\[t.htm]t'!$I$7")

Whatever value is in A1 - and assuming it matched the folder name exactly - would be used as the folder name in the formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

February 12, 2011 at 18:47:15
Thank you so much! I greatly apologize for the mis use of terms. Im sure it makes it eve more confusing. As you can probably tell, I am definitely ignorant with excel usage. I've opted for option 1 at the moment due to time constraints. thanks again!

Report •

Ask Question