Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MDL2005
 
Posts: n/a
Default 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   Report Post  
Duke Carey
 
Posts: n/a
Default

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   Report Post  
Duke Carey
 
Posts: n/a
Default

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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel to Excel, Cut & Paste, 1 cell, text, multiplies file size f. PboB Excel Discussion (Misc queries) 3 March 16th 05 07:17 PM
looking for a formula Amanda Excel Worksheet Functions 5 January 5th 05 08:37 AM
using content of a cell in a formula in another cell mpierre Excel Worksheet Functions 3 December 28th 04 04:43 PM
Formula Result Correct but value in the cell is wrong jac Excel Worksheet Functions 2 December 17th 04 09:05 PM
Copy Word table into Excel cell by cell hg Excel Discussion (Misc queries) 3 December 15th 04 05:43 PM


All times are GMT +1. The time now is 02:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"