#REF!

='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40

Gives me #ref!

I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
Want it to automaticly Take date value from A2 - 1 year and put it in 
the path for the CAF-Jan.xls file.
0
AMDGUY (13)
2/2/2005 1:19:14 PM
excel 39879 articles. 2 followers. Follow

8 Replies
564 Views

Similar Articles

[PageSpeed] 16

Sry removed previous post so I could post in both groups

Jerry W. Lewis wrote:
> See answer to your previous post.  Repeating a question after 18 minutes 
> does nothing to spead a reply.
> 
> Jerry
> 
> AMDGUY [MCP] wrote:
> 
>> ='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40
>>
>> Gives me #ref!
>>
>> I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
>> Want it to automaticly Take date value from A2 - 1 year and put it in 
>> the path for the CAF-Jan.xls file.
> 
> 
0
AMDGUY (13)
2/2/2005 1:28:55 PM
See answer to your previous post.  Repeating a question after 18 minutes 
does nothing to spead a reply.

Jerry

AMDGUY [MCP] wrote:

> ='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40
> 
> Gives me #ref!
> 
> I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
> Want it to automaticly Take date value from A2 - 1 year and put it in 
> the path for the CAF-Jan.xls file.

0
post_a_reply (1395)
2/2/2005 1:29:02 PM
Also I can not figure out how to use the indirect() function
Could you give me an example base on the question?

Jerry W. Lewis wrote:
> See answer to your previous post.  Repeating a question after 18 minutes 
> does nothing to spead a reply.
> 
> Jerry
> 
> AMDGUY [MCP] wrote:
> 
>> ='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40
>>
>> Gives me #ref!
>>
>> I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
>> Want it to automaticly Take date value from A2 - 1 year and put it in 
>> the path for the CAF-Jan.xls file.
> 
> 
0
AMDGUY (13)
2/2/2005 1:37:02 PM
=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
0")


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"AMDGUY [MCP]" <AMDGUY@nospam.com> wrote in message
news:uKdOIxSCFHA.2876@TK2MSFTNGP12.phx.gbl...
> Also I can not figure out how to use the indirect() function
> Could you give me an example base on the question?
>
> Jerry W. Lewis wrote:
> > See answer to your previous post.  Repeating a question after 18 minutes
> > does nothing to spead a reply.
> >
> > Jerry
> >
> > AMDGUY [MCP] wrote:
> >
> >> ='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40
> >>
> >> Gives me #ref!
> >>
> >> I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this
changes.
> >> Want it to automaticly Take date value from A2 - 1 year and put it in
> >> the path for the CAF-Jan.xls file.
> >
> >


0
bob.phillips1 (6510)
2/2/2005 2:21:34 PM
I still get the #REF! with that

Bob Phillips wrote:
> =INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
> 0")
> 
> 
0
AMDGUY (13)
2/2/2005 2:36:03 PM
You need to have the other workbook open when using indirect


Regards,

Peo Sjoblom

"AMDGUY [MCP]" wrote:

> I still get the #REF! with that
> 
> Bob Phillips wrote:
> > =INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
> > 0")
> > 
> > 
> 
0
PeoSjoblom (789)
2/2/2005 2:59:04 PM
I did have the other one open.

do you know if there is another way that you dont have to have the 
workbook open?

Peo Sjoblom wrote:
> You need to have the other workbook open when using indirect
> 
> 
> Regards,
> 
> Peo Sjoblom
> 
> "AMDGUY [MCP]" wrote:
> 
> 
>>I still get the #REF! with that
>>
>>Bob Phillips wrote:
>>
>>>=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
>>>0")
>>>
>>>
>>
0
AMDGUY (13)
2/2/2005 3:03:26 PM
It works if the other workbook is open but then you don't need the path, for 
possible workarounds see

http://makeashorterlink.com/?F2993260A


Regards,

Peo Sjoblom

"AMDGUY [MCP]" wrote:

> I did have the other one open.
> 
> do you know if there is another way that you dont have to have the 
> workbook open?
> 
> Peo Sjoblom wrote:
> > You need to have the other workbook open when using indirect
> > 
> > 
> > Regards,
> > 
> > Peo Sjoblom
> > 
> > "AMDGUY [MCP]" wrote:
> > 
> > 
> >>I still get the #REF! with that
> >>
> >>Bob Phillips wrote:
> >>
> >>>=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
> >>>0")
> >>>
> >>>
> >>
> 
0
PeoSjoblom (789)
2/2/2005 3:29:08 PM
Reply:

Similar Artilces:

#Ref! in cells of row linked to deleted row
Does one just delete the row? Why wasn't it just deleted like the row i the database since it was linked to it? Thanks, Trud -- lburg80 ----------------------------------------------------------------------- lburg801's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2833 View this thread: http://www.excelforum.com/showthread.php?threadid=48026 ...

Turn Off Updating of Ref Fields in Word 2007
I have a document that someone used Ref fields to link to bookmarked figures and hyperlinked them to the paragragh thus inserting a copy of the figure everywhere they inserted the field. I unlinked the extra graphics from the paragraph but not from the actual text e.g. "Figure VII 2-2." Now the document has updated all of the fields and reinserted the figures back in. If it was one or two no big deal but we are talking about probably 75-100 extra graphics being inserted into the document. I need to turn off the Auto Updating of Fields on Save or whenever the heck i...

Macro to switch to another sheet, grab ref to any cell, paste ref in current sheet??
I am looking for a way (either macro or VBA) to perform one simple task: while in *any cell* of your choice in the current worksheet, switch to another specific sheet, choose *any* cell, grab a reference to that cell (not its value), switch back to the first worksheet and slap in the reference of the cell in the other sheet. This would be the automated equivalent of doing the following manually: 1) Select *any cell* (say *F11*) in the current sheet (Call it "*Destination*"). 2) Press "=" (no "ENTER") 3) Click the tab for the other sheet (call it "*Source*&...

#REF!
='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40 Gives me #ref! I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes. Want it to automaticly Take date value from A2 - 1 year and put it in the path for the CAF-Jan.xls file. Sry removed previous post so I could post in both groups Jerry W. Lewis wrote: > See answer to your previous post. Repeating a question after 18 minutes > does nothing to spead a reply. > > Jerry > > AMDGUY [MCP] wrote: > >> ='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[...

Cross-Ref to Excel Chart
When I insert a cross reference to the caption of a linked Excel chart, the cross reference inserts the chart as well as 'Figure 6' or whatever. I can delete the chart from the cross reference, but when I print the document it comes back again. I can't see anything in the field codes causing this, though there is a /* MERGEFORMAT switch that I don't recognise I have a bunch of these linked charts in the document, and not all of them behave like this. It doesn't happen with spreadsheet data that I have linked to the Word doc, only the charts. oops, pr...

REF# in Sendmail
I am getting a REF# again where there is formulas when using Sendmail. My fix the last time was divideing up the formulas so they wouldnt have so many sheets to sum up. What I have now is this.... Formula is suming up the same cell in many sheets. No matter what the first sheet is in the formula, if I have a value from it coming into the sum formula I get REF# instead when the sheet has gone through Sendmail and stripped. These are order sheets. When I put a fake sheet in for the first sheet in the formula, and didnt put an order on it, everything was ok. Any ideal what would cause...

AA Distribution Query to include Trx Desp, Dist Ref and AA Ref
AA Distribution Query should be able to include Transaction Description, Distribution Reference and AA Reference. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=6a646714...

#ref!
#REF! appears in many of my formula cells at the moment which isn't a problem. I wondered if this could be replaces with a value like 0. Maybe with one single formula or macro which works for the entire sheet? Any ideas? --- Message posted from http://www.ExcelForum.com/ Ian Most functions that may return an error value should be 'wrapped' in one of the error functions. These are ISNA, ISERR and ISERROR. ISNA traps #N/A! errors ISERR traps all errors except #N\A! ISERROR traps all errors In a VLOOKUP for example you could use =IF(ISERROR(VLOOKUP(Ref,Range,Offset,FALSE)),&...

What are the problems with ref. a large range?
Does referencing a huge range use more resources for example does........ =IF(ISNA(LOOKUP(C6,LSM_Summary_ADC!O3:O65000,LSM_Summary_ADC! F3:F65000)),"x",LOOKUP(C6,LSM_Summary_ADC!O3:O65000,LSM_Summary_ADC! F3:F65000)) use more resource then say ....... =IF(ISNA(LOOKUP(C6,LSM_Summary_ADC!O3:O100,LSM_Summary_ADC! F3:F100)),"x",LOOKUP(C6,LSM_Summary_ADC!O3:O100,LSM_Summary_ADC! F3:F100)) I need to allow for an expanding data range and don't want to keep extending it. any help appreciated...... many thanks mike try On the Summary_ADC sheet>insert>name>define>n...

alter column ref
my column references are displayed in numeric rather than alphabetic. eg. the first column is 1, instead of A. Can anyway help me? thanks Kaz Excel 2003:- Tools>>Options>>General>>Settings>>Uncheck the R1C1 reference style and give ok. Excel 2007:- Press Alt+T+O>>Formulas>>Working with formulas>> Uncheck the R1C1 reference style and give ok. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Kaz" wrote: > my column references are displayed in numeric rather th...

How do I copy a formula with relative ref. keeping the same ref.?
How do I copy cells with relative references into other cells kepping the same references? e.g. cell A3 has the formula A1+A2. A6 has the formula A4+A5. I want to copy A3 and A4 into A15 and A16 in order to have A15 with the formula A1+A2 and A16 with the same formula as A6 (A4+A5). Hi Change the formula to =$A$1+$A$2 Look also in the Excel help -- Regards Ron de Bruin http://www.rondebruin.nl "avjunior" <avjunior@discussions.microsoft.com> wrote in message news:38ACCFCC-04D7-4921-B45C-B49BAB29D474@microsoft.com... > How do I copy cells with relative references i...

#REF
I need to send to my friend a file with links cells to another files in my computer. But when my friend open the file, Excel ask about to update the links and he answer no update, but when the file open the cells when this links show #REF message. How can i do to send my file with the information in the linked cells without excel show me the #REF message? Thanks Copy all formulas, then paste them unto a new workbook and paste special as values, then send that workbook Regards, Peo Sjoblom "QK" <QK@discussions.microsoft.com> wrote in message news:6F5C895E-0A58-45AB-A2F...

#REF#
For a file with linked files, every so often I get a "#REF#" in some of the cells. After I update the links, the problem goes away - to come back the next time the file is opened. There's not a problem with the formula, nor with the file that it is linked. Any ideas? It sounds like the workbook was developed in an earlier version of excel than the one you're currently using. The newer versions of excel want to recalculate when you open a workbook and it notices that the workbook was created in an older version. Maybe you could just refresh the links. (I think some h...

Formula
Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through ...

Copying a formula with different sheet refs
I need to fill in the formula, "=IF(MAX(Sheet2!B5:B200)>(TODAY()-21),"not due","due")" in sheet1 from cell b2 to B200 but to change only the Sheet ref so that b3 refers to sheet3, and b4 refers to sheet4 and so on. When I try to fill down, Excel changes the range of cells, not the sheet nos and if I try to copy and paste, the same thing happens. Would be grateful of any help please. ...

#REF #2
I am using Excell 2002 at the office. I have a problem viewing a spreadsheet that a coworker sent me, which she prepared in Excell 2000. Some of the cells display #REF, instead of the formula value. Any ideas on how to resolve this would beappreciated. Thanks. Barry You may be missing an add-in such as the Analysis ToolPak Navigate: Tools,Addin "Barry" wrote: > I am using Excell 2002 at the office. I have a problem viewing a spreadsheet > that a coworker sent me, which she prepared in Excell 2000. Some of the > cells display #REF, instead of the formula valu...

#REF!
Two worksheets sheet are used to feed a chart on a third sheet. One accepts the results of a query that's triggered by a macro from a button on an outline (a forth sheet). The second sheet is a worksheet that simply takes data from the query sheet and permits it to be manipulated. The data is transfer by simply "=" in a cell then a link to the respective cell on the query sheet. When I run the macro, the cells on the worksheet report "#REF!". Anything linked to that updated query sheet reports "#REF!". What am I doing wrong and how do I prevent this? I could...

ref parameter
1. What actually happens when a parameter to a method is passed by ref? 2. And, whether the same thing happens while we pass a parameter by out? 3. Whether boxing/unboxing happens while we pass parameter by ref and get the value? Thank you Regards Raj Raj wrote: > 1. What actually happens when a parameter to a method is passed by ref? That's a very open-ended question. From the language perspective, whatever variable is used as the argument is essentially aliased by the argument. That is, changes to the argument in the method will be reflected in the variable...

Converting RC cell refs to cell range refs
Is there a function built into Excel that returns the alphabetic column reference if you provide the numeric column reference and visa versa? Currently I am using a seperate hidden sheet listing all the alphabetic column references down column A. Then returning the value for Range("A & v_column_no). This is obviously not ideal but has worked for the short term. Many thanks Dave Ralph "David Ralph" <david_r_ralph20@hotmail.com> wrote in message news:brklbv$r4r$1@visp.bt.co.uk... > Is there a function built into Excel that returns the alphabetic column > re...

#REF! #2
What does this error mean? Is there a way to prevent it from ending up in my worksheet functions?\ Thanks. Alex Hi Alex looks like you're having a 'bad' cell reference (see Excel helpfile). It would be helpful if you post the formula which creates this error. Frank Alex wrote: > What does this error mean? Is there a way to prevent it > from ending up in my worksheet functions?\ > > Thanks. > Alex "Alex" <anonymous@discussions.microsoft.com> wrote in message news:8b9101c3e9ad$27ddd7d0$a501280a@phx.gbl... > What does this error mean? Is th...

Macro #REF!
is there anyhthing I can do to reference the first 35 cells of a seperate worksheet, but those 35 cells I am referencing change constantly, and with what I have now does not work because every time a cell changes that I am referencing I get a #REF! error. Is there a way I can change this? As ALWAYS, post YOUR code for comments -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "daphoenix" <daphoenix@discussions.microsoft.com> wrote in message news:9DB6AD2D-2638-4602-9926-6E9905CC7700@microsoft.com... > is there anyhthing I can do to referen...

Absolute ref
Hi All, I have a custom function in the following form: Function Test(A,B,C) Users select A, B and C, enter the function in say B1 and then copy it down the column. What I am looking for is a way to make A as absolute and B and C reletaive reference. One way is that the user inserst $ signs manually. Is there another way? Can i do it automatically? (I asked this question in another MS group sometimes ago with no workable answer) Thanks for your help. Regards Sadik Hi instaed of inersting the '$' sign manually the user can hit F4 to toggle between absolute and relative refe...

Database to Excel REF#!
Hello, I have a spreadsheet that feeds from a database. If it meets the criteria of an "HL" call in a particular field, it shows on the spreadsheet, no problem. What the spreadsheet is for it to notify when and "HL" call comes up so the user can handle the call quickly. They change it from "HL" to a variety of different things and it comes off the list like it's supposed to. The problem is that when the field is changed from "HL" to anything else, like it is supposed to, the spreadsheet changes all of the calculation fields to REF#! ...

#REF with vlookup
Hi I am trying to match 2 addresses and return a text value (looks like a number). The list exceeds the Excel max row number, so needs to be split over 2 sets of colums ie B2:B65000 &.H2:H38410 Any help would be appreciated!! =IF(ISTEXT(VLOOKUP(B2 &"",Sheet2!B$2:B$65001,3,FALSE))= TRUE, " ",VLOOKUP(B2 &"",Sheet2!$H2:H$38410,9,FALSE)) Get #REF The address is identical in both worksheets??? In both cases, you are looking up into a single column (either B or H), but your column references are outside that (3 and 9). You either need to fix your l...

#REF Links
Why do I get a #REF error or #VALUE error with a spreadsheet tha calculates from another workbook? If I go to edit links and open th source document, the formulas "magically" fix themselves. Still, it' frustrating having to open the source file to fix -- rndyda ----------------------------------------------------------------------- rndydav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3533 View this thread: http://www.excelforum.com/showthread.php?threadid=55103 what formulas? Some, such as INDIRECT only work on OPEN workbooks. -- Don Guillet...