Linking a reference within a formula

[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?

 

[Luke M]

Check out the INDIRECT function.

 

[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.

 

[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. 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.

 

[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?

 

[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. 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

='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

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

--
Biff
Microsoft Excel MVP





.