| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking a reference within a formula
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking a reference within a formula
Check out the INDIRECT function.
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "phd4212" wrote: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking a reference within a formula
The cell that I would like to reference is linked in a different
worksheet--so does that cause the indirect function to be moot? "Luke M" wrote: Check out the INDIRECT function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "phd4212" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking a reference within a formula
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. -- Biff Microsoft Excel MVP "phd4212" wrote in message ... The cell that I would like to reference is linked in a different worksheet--so does that cause the indirect function to be moot? "Luke M" wrote: Check out the INDIRECT function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "phd4212" wrote: 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking a reference within a formula
='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" wrote: 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. -- Biff Microsoft Excel MVP "phd4212" wrote in message ... The cell that I would like to reference is linked in a different worksheet--so does that cause the indirect function to be moot? "Luke M" wrote: Check out the INDIRECT function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "phd4212" wrote: 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? . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking a reference within a formula
='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/300...-10423159.html -- Biff Microsoft Excel MVP "phd4212" wrote in message ... ='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" wrote: 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. -- Biff Microsoft Excel MVP "phd4212" wrote in message ... The cell that I would like to reference is linked in a different worksheet--so does that cause the indirect function to be moot? "Luke M" wrote: Check out the INDIRECT function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "phd4212" wrote: 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? . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking a reference within a formula
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. -- If this helps, please remember to click yes. "phd4212" wrote: 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking a reference within a formula
The cell that I would like to reference is linked in a different
worksheet--so does that cause the indirect function to be moot? "Paul C" wrote: 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. -- If this helps, please remember to click yes. "phd4212" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| linking a worksheet by a cell reference | Excel Worksheet Functions | |||
| Absolute reference when linking workbooks | Excel Discussion (Misc queries) | |||
| Linking Cell Reference | Excel Discussion (Misc queries) | |||
| Paste Linking with Absolute Reference | Excel Discussion (Misc queries) | |||
| How Do I Get a date reference when linking excel worksheets? | Excel Worksheet Functions | |||
Hybrid Mode
