Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use formular to create a cell address
I have a weekly balance shhet saved by the date in the filename. Each week
the date changes so I have to manually changed the date in the cell address to get the value. I want to use the curent date take 7 days off add some text so it comes up with the file name. I can get the correct text but i get a &ref error the date in C3 is 16/05/09 and i want to create ='[09-05-09 test.xls]Sheet1'!$B$3 to get the value from last week using ='["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy") & " test.xls]sheet1'!$B$3 keep getting #REFF error. Anyone can you help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use formular to create a cell address
I cannot test this as I have not the time to make files with odd names
but you need to use the INDIRECT function as in =INDIRECT("'["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy") & "test.xls]sheet1'!$B$3") I would break this up, placing ="'["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy")part is cell A1 (say) and sheet name ="test.xls]sheet1'!" in A2 (say) then =INDIRECT(A1&A2&"$B$3") best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ridingpeter" wrote in message ... I have a weekly balance shhet saved by the date in the filename. Each week the date changes so I have to manually changed the date in the cell address to get the value. I want to use the curent date take 7 days off add some text so it comes up with the file name. I can get the correct text but i get a &ref error the date in C3 is 16/05/09 and i want to create ='[09-05-09 test.xls]Sheet1'!$B$3 to get the value from last week using ='["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy") & " test.xls]sheet1'!$B$3 keep getting #REFF error. Anyone can you help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use formular to create a cell address
I bet you could use this, too:
=text($c$3-7,"dd-mm-yy") But if that sending workbook is closed you'll get an error. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. ridingpeter wrote: I have a weekly balance shhet saved by the date in the filename. Each week the date changes so I have to manually changed the date in the cell address to get the value. I want to use the curent date take 7 days off add some text so it comes up with the file name. I can get the correct text but i get a &ref error the date in C3 is 16/05/09 and i want to create ='[09-05-09 test.xls]Sheet1'!$B$3 to get the value from last week using ='["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy") & " test.xls]sheet1'!$B$3 keep getting #REFF error. Anyone can you help -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create a cell reference from ADDRESS function return? | Excel Worksheet Functions | |||
How to create table of cell names with the name's cell address | Excel Discussion (Misc queries) | |||
Formular to add to a IP address | Excel Discussion (Misc queries) | |||
Create chart at a cell address? | Excel Programming | |||
Create Right Click Option to Copy Cell Address | Excel Programming |