Linking a reference within a formula

P

phd4212

Is it possible to have a link to another cell that is derived from a
concatenate formula?

I have a spread sheet that is created weekly that displays our daily
forecasts. Each week is stored in the same spreadsheet, but the individual
day's forecast location differs. I've created a concatenate formula that
gives me a link to the formula as it should read in the spreadsheet, but I
cannot get the data in the other spreadsheet to populate....any help?
 
P

Paul C

the indirect function will do this

=INDIRECT(CONCATENATE("A","1")) entered in cell behaves like =A1

The INDIRECT function is a volitile function (recalculated everytime any
recalulation is triggerd even is the cell with the fuction is not impacted)
and overuse can dslow dow recalculations.
 
P

phd4212

The cell that I would like to reference is linked in a different
worksheet--so does that cause the indirect function to be moot?
 
T

T. Valko

I've created a concatenate formula that gives me a link
The cell that I would like to reference is linked in a
different worksheet--so does that cause the
indirect function to be moot?

No. Post your concatenate formula so we can see what you're trying to do.
 
P

phd4212

The cell that I would like to reference is linked in a different
worksheet--so does that cause the indirect function to be moot?
 
P

phd4212

='K:\Actual Orders vs Forecast\2010 Actual vs Forecast\[2.15.10 to
2.19.10.xls]Friday'!$D$3

That is currently the hyperlink I have and I have to manually change the day
every day. I have written the concatenate formula

=CONCATENATE("='K:\Actual Orders vs Forecast\2010 Actual vs
Forecast\[",TEXT(F1,"mm.dd.yy"), " to
",MONTH(F1+4),".",DAY(F1+4),".",RIGHT(YEAR(F1+4),2),".xls]",TEXT(C1,"dddd"),"'!$G$3")

With F1 holding the Monday of the Week; c1 containing the current day
 
T

T. Valko

='K:\Actual Orders vs Forecast\2010 Actual vs Forecast\[2.15.10 to
2.19.10.xls]Friday'!$D$3

Since the link is to another file, using INDIRECT will require that the
other be open. This is usually undesireable.

There is a free add-in called Morefunc.xll that has a function called
INDIRECT.EXT that might work for you. It works much like the built-in
INDIRECT function except that the source file doesn't need to be open.

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

--
Biff
Microsoft Excel MVP


phd4212 said:
='K:\Actual Orders vs Forecast\2010 Actual vs Forecast\[2.15.10 to
2.19.10.xls]Friday'!$D$3

That is currently the hyperlink I have and I have to manually change the
day
every day. I have written the concatenate formula

=CONCATENATE("='K:\Actual Orders vs Forecast\2010 Actual vs
Forecast\[",TEXT(F1,"mm.dd.yy"), " to
",MONTH(F1+4),".",DAY(F1+4),".",RIGHT(YEAR(F1+4),2),".xls]",TEXT(C1,"dddd"),"'!$G$3")

With F1 holding the Monday of the Week; c1 containing the current day


T. Valko said:
No. Post your concatenate formula so we can see what you're trying to do.

--
Biff
Microsoft Excel MVP





.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top