Home |
Search |
Today's Posts |
#1
|
|||
|
|||
EXCEL LINK CELL FORMULA
Hi
I've written a spreadsheet to to do a weekly forecast of products. 7 Days along the columns at the top and all my products down the rows basically I input (painstakingly) information from other spreadsheets week on week, until I learnt i could link to the another spreadsheets im currently using. This is a lot faster for me but what i need to know is is there a way i can specify the files by using the date on the column heading to input the files from the link instead of me manually inputting them every week eg in g3 the date 14/03/05 for Monday forecast in g7 the data drawn from the relevant stored spreadsheet the equation in g7 is =sum('[Daily1403.xls]Forecast'!$c$19) the figure is being taken from a stored spreadsheet named Daily1403.xls and the information is on tab Forecat in cell c19 What im trying to do is if I change the date in cell G3 to 21/03/05 it will automatic changed the formula in the cell to Daily2103.xls instead Is this possible If so can anybody help?? Thanks -- MDL2005 |
#2
|
|||
|
|||
First, you can just directly reference the cell without the SUM() function
=[Daily1403.xls]Forecast!$c$19 With the Dailyxxxx files open this will work for cell G7 =INDIRECT("[Daily"&TEXT(DAY(G$3),"00")&TEXT(MONTH(G$3),"00")&" .xls]Forecast!C19") However, if you cannot copy this to other cells and have Excel adjust the cell reference. If each sheet has data in column C, then put this formula in G7 and copy it to all your other linked cells =SUM(INDIRECT("'[Daily"&TEXT(DAY(G$3),"00")&TEXT(MONTH(G$3),"00")&" .xls]Forecast'!r"&(12+ROW())&"c3",FALSE)) I'm not sure, but I think the INDIRECT() function requires the linked workbooks to be open to pull the data in Hope this helps "MDL2005" wrote in message ... Hi I've written a spreadsheet to to do a weekly forecast of products. 7 Days along the columns at the top and all my products down the rows basically I input (painstakingly) information from other spreadsheets week on week, until I learnt i could link to the another spreadsheets im currently using. This is a lot faster for me but what i need to know is is there a way i can specify the files by using the date on the column heading to input the files from the link instead of me manually inputting them every week eg in g3 the date 14/03/05 for Monday forecast in g7 the data drawn from the relevant stored spreadsheet the equation in g7 is =sum('[Daily1403.xls]Forecast'!$c$19) the figure is being taken from a stored spreadsheet named Daily1403.xls and the information is on tab Forecat in cell c19 What im trying to do is if I change the date in cell G3 to 21/03/05 it will automatic changed the formula in the cell to Daily2103.xls instead Is this possible If so can anybody help?? Thanks -- MDL2005 |
#3
|
|||
|
|||
Should have followed my own advice and left the SUM() function out of that.
Try this instead: =INDIRECT("'[Daily"&TEXT(DAY(G$3),"00")&TEXT(MONTH(G$3),"00")&" .xls]Forecast'!r"&(12+ROW())&"c3",FALSE) "Duke Carey" <duke_No_Junk_carey_at_hotmail.com wrote in message ... First, you can just directly reference the cell without the SUM() function =[Daily1403.xls]Forecast!$c$19 With the Dailyxxxx files open this will work for cell G7 =INDIRECT("[Daily"&TEXT(DAY(G$3),"00")&TEXT(MONTH(G$3),"00")&" .xls]Forecast!C19") However, if you cannot copy this to other cells and have Excel adjust the cell reference. If each sheet has data in column C, then put this formula in G7 and copy it to all your other linked cells =SUM(INDIRECT("'[Daily"&TEXT(DAY(G$3),"00")&TEXT(MONTH(G$3),"00")&" .xls]Forecast'!r"&(12+ROW())&"c3",FALSE)) I'm not sure, but I think the INDIRECT() function requires the linked workbooks to be open to pull the data in Hope this helps "MDL2005" wrote in message ... Hi I've written a spreadsheet to to do a weekly forecast of products. 7 Days along the columns at the top and all my products down the rows basically I input (painstakingly) information from other spreadsheets week on week, until I learnt i could link to the another spreadsheets im currently using. This is a lot faster for me but what i need to know is is there a way i can specify the files by using the date on the column heading to input the files from the link instead of me manually inputting them every week eg in g3 the date 14/03/05 for Monday forecast in g7 the data drawn from the relevant stored spreadsheet the equation in g7 is =sum('[Daily1403.xls]Forecast'!$c$19) the figure is being taken from a stored spreadsheet named Daily1403.xls and the information is on tab Forecat in cell c19 What im trying to do is if I change the date in cell G3 to 21/03/05 it will automatic changed the formula in the cell to Daily2103.xls instead Is this possible If so can anybody help?? Thanks -- MDL2005 |
#4
|
|||
|
|||
Hi
It will be much easier, when you save your daily workbooks with fixed names. P.e. You save them as Day1.xls, Day2.xls, ..., Day7.xls Now you can hard-code all links in forcast workbook. In next week, before you open forecast workbook, you simply copy new dayly workbooks into folder and rename them as Day1.xls, Day2.xls, etc. Now, when you open the forecast workbook, all new info is there. Btw. is the so much of info in dayly workbooks, that you need separate workbook for every day. Otherwise you could have a sheet for every weekday in same workbook. I myself prefer even more compact design - a single input table, which contains date as one of key fields. Depending on amount of data, the table is created for some fixed period - month or year usually. Workbooks name is neutral, like Production.xls. When the period ends, then the workbook is archived (Save As) under different name, like Production2004.xls, and after this all data entries are removed from original input workbook (but all predefined formulas are left intact) - so the workbook will be ready for new period. Or sometimes I write a code, which automatically or by user intervention removes all entries, older than preset number of days, into some archive workbook. When there is a need to view or print data for some certain date or week or department or whatever, then I design report sheets, where user can set conditions, and all data is read from input sheet into report sheet. No hundreds of workbooks to be confused with. And by such design, your forecast workbook design wil be a lot easier too. Mainly, because it is relatively easy to read data from single table by some set of conditions, but the formulas have a tendency to get too cumbersome, when you have to read the info from several sheets. And with all essential data in single table, it s possible to read data from input table, using ODBC query - sometimes it results in remarcable increase of perfomance, because you don't have any links on your forecast workbook anymore, and you can design the query in a way, where only needed data are returned. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "MDL2005" wrote in message ... Hi I've written a spreadsheet to to do a weekly forecast of products. 7 Days along the columns at the top and all my products down the rows basically I input (painstakingly) information from other spreadsheets week on week, until I learnt i could link to the another spreadsheets im currently using. This is a lot faster for me but what i need to know is is there a way i can specify the files by using the date on the column heading to input the files from the link instead of me manually inputting them every week eg in g3 the date 14/03/05 for Monday forecast in g7 the data drawn from the relevant stored spreadsheet the equation in g7 is =sum('[Daily1403.xls]Forecast'!$c$19) the figure is being taken from a stored spreadsheet named Daily1403.xls and the information is on tab Forecat in cell c19 What im trying to do is if I change the date in cell G3 to 21/03/05 it will automatic changed the formula in the cell to Daily2103.xls instead Is this possible If so can anybody help?? Thanks -- MDL2005 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel to Excel, Cut & Paste, 1 cell, text, multiplies file size f. | Excel Discussion (Misc queries) | |||
looking for a formula | Excel Worksheet Functions | |||
using content of a cell in a formula in another cell | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions | |||
Copy Word table into Excel cell by cell | Excel Discussion (Misc queries) |