Free Microsoft Excel 2013 Quick Reference

Invalid reference Results

I keep getting an error that A formula in tgis worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference.

If Excel is smart enough to know this why doesn't it tell me which formula has an invalid reference? Is there a way to find it without looking at every single formula?

I've got a problem with a file I have inherited. It is very complex but works beautifully,... until now. The Macro suddenly has a run time error and crashes out on the first line. When I back the data up by one month, it works fine. There is no change in the script at all. The previous manager of this file mentioned something in passing about how the macro doesn't work right if the file is copied (or something like that) for some unknown reason.

"Run Time Error 1004""

"Reference is not valid"

Please Advise.
Thanks,

Hi,

I have a problem that keeps re-occuring. Whenever I have a chart linked to a table of data, deleting a row or column causes a invalid references message. This is definitely derived from the chart as deleting it solves the problem.

Checking the series data in the chart there are no visible reference errors. Even deleting every series does not fix this.

Any help is appreciated this is causing me grief.

Thanks
Grant

Hi All,

Using Excel 2003 I have written some code that uses the 'Microsoft Word 11.0 Object Library' which works well. Some of my code also relates to the 'Microsoft Excel 11.0 Object Library' and the 'Microsoft Office 11.0 Object Library'.

When the file is saved and reopened in Excel 2003 all works well however if I open the file in say Excel 2000 an error message is generated that relates to a reference error.

When I check the references I find that the references checked are: 'Microsoft Excel 9.0 Object Library' and the 'Microsoft Office 9.0 Object Library' both of which are OK but there is a refererence 'Microsoft Word 11.0 Object Library' which is indicated as missing.

If I search for and select the 'Microsoft Word 9.0 Object Library' the macros run properly again.

If I save the file in Excel 2000 and open it in Excel 2003 the file works perfectly and when I check the references the all refer to 11.0 object libraries.

Can any one advise how to ensure the reference to the Microsoft Word Object Library correctly adjusts for an earlier version of excel. At present the only way I can provide this file for others is to save an operating version in Excel 2000 before distribution - but if they open it in Excel 2003 and then try to run it with Excel 2000 it will crash.

Thank you for any help.

Regards,

CDT

due to invalid cell reference, cell is = '#REF!'

i m unable to get data as '#REF!' to my variable
It is saying type mismatch error.

i need to get value to my variable same as any error reference.

I used to have this working, but now it's broken for some reason. I have created a weight chart, but the values that go into the chart are kept in another xlxs file. I tried opening both workbooks and when selecting data range, picking the cells from the values workbook, but excel keeps throwing an error about invalid reference.

I've searched around and just find basic chart creation tutorials. Does anyone have any recommendations? I'm running Windows 7 Ultimate 64, Excel 2007 Enterprise.

EDIT: Uploaded files

I am struggling to figure out why, when working with a macro written in WinBatch (guiding) Excel (not my idea or fault) when WinBatch launches the
!EG function (GoTo) Excel keeps the current cell reference in the Reference field and therefore, the winbatch string which adds a variable cell reference ends up appending itself onto this shadow cell reference and is considered an invalid reference.

This doesn't occurr on the original machine (the folks who like SendKeys) but it does on mine. I have looked through all the Excel options/settings, making sure they are the same between these machines, but I can't find anything different...I must be missing something. Any ideas?

Hello, New to the Board but really need some help.

I work for a large organisation (6000 + PCs). I work on a team where we capture info on a excel spreadhsheet that has embedded macros. These workbooks get sent to different users who add their contribution to the workbook and save the changes to the workbook before returning to me.

The problem is that different users are using differnt versions of MS office, which means that if someone opens the document in office 2003 and saves the changes, the office 2000 reference is replaced by the office 2003 library, so when i get the file back it crashes and i have to change the reference manually.

I flagged this up weeks ago as an issue in the hope that i could amend the excel workbook we send out with something like :- (which would run on workbook open)

Set ref = Application.VBE.ActiveVBProject.References("Outlook")
Application.VBE.ActiveVBProject.References.Remove ref
Application.VBE.ActiveVBProject.References.AddFromFile_ "D:ApplicationsOffice2000OFFICE9msoutl.olb"

Sadly this option was not embraced, more PCs have been upgraded to Office 2003 and this is a time consuming issue

What i want to do is have a 'Master tool' that opens each template and sucks the new data out of them, fixing teh invalid reference as it goes. The reference MUST be fixed becuase i might forward the document onto another user that doesnt have Office 2003, and they will experience the same issue

Can i do something similar to the above code, but not in the active project.

something like :-

Dim wbsource as workbook

set ref = wbsource.application.vbe.activevbproject.references ("Outlook")

Blah blah.

So im affecting the VBE of the damaged excel workbook rather than the active project?

I tried it but get an error saying it conflicts with an existing library

Am i on the right lines?

The Master tool will user Microsfot Outlook object library 9.0 and so will the excel workbooks i send out. But when i receive the excel workbook back it will contain "Microsfot Outlook object library 10.0" which wont be on my PC, hence i want the code to automaticall open, replace the invalid reference with a valid one and close it down again.

Hope ive not over empasised. Any thoughts greatfully received

Verify that your formulas contain a valid path, workkbook, range name, and
cell reference.

How can I search them.

I am getting an error message in Excel 2007 that advises me that the file
version cannot contain formulas that reference cells beyond the worksheet
size of 256 columns or 65,536 rows. Both the worksheets involved in the
function are in Excel 2007

I'm beginning my foray into VBA and, not suprisingly, am now stuck.
The workbook is far too large to post, so here's some background:
- - - - - - - - - - - - -
I'm receiving an error in my VBA at the point where I'm attempting to sort. This is the error message:
Run-time error '1004':
The sort reference is not valid.  Make sure that it's within the data you want to sort, and the first Sort By box isn't the
same or blank.
My workbook consists of the following sheets:
Orders
SortSheet
Operations
Staging
BWInput
Analysis
Summary
I'm attempting to move/manipulate/sort data from the Orders & Operations sheets (via the SortSheet & Staging Sheets) to the BWInput sheet with this VBA:
Sub SetupOptimizer()
    ' Move two columns to a better location
    Range("Orders!Z:AA").Cut Destination:=Range("Orders!T:U")
 
    ' Move the block of raw orders data to the sort sheet
    Range("OrdersData").Copy Destination:=Range("SortSheet!E2")
 
    ' Get rid of the redundant data on the orders sheet
    Range("tblOrders").Clear
 
    ' Autofill the formulas down alongside the orders
    Range("SortSheet!A2:D2").AutoFill Destination:=Range("SortCodeDestination")
 
    ' Ensure the formulas run
    Calculate
 
    ' Sort the orders based on the formulas
    Range("tblSort").Sort _
        Key1:=Range("A2"), _
        Order1:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 
    ' Move the sorted orders to the next step
    Range("SortResults").Copy Destination:=Range("Staging!A2")
 
    ' Put another copy of the sorted orders on the analysis sheet
    Range("Analysis!C9").PasteSpecial (xlPasteValues)
 
    ' Get rid of the redundant data on the SortSheet sheet
    Range("tblSort").Clear
 
    ' Autofill the formulas down alongside the sorted orders
    Range("Staging!Z2:EV2").AutoFill Destination:=Range("StagingCodeDestination")
 
    Calculate
 
    Range("tblStaging").Copy
 
    ' Replace the formulas with values
    Range("tblStaging").PasteSpecial (xlPasteValues)
 
    Range("tblStaging").Copy
 
    ' Move the resulting block of data to the BWInput sheet
    Range("BWInput!A1").PasteSpecial (xlPasteAll)
 
    ' Get rid of the redundant data on the operations sheet
    Range("tblOperations").Clear
 
    ' Get rid of the redundant data on the staging sheet
    Range("tblStaging").Clear
 
    ' Remove unnecessary rows from the Analysis sheet
    Range("AnalysisTrim").Clear
 
    Calculate
 
    ' Go to the summary sheet to finish
    Range("Summary!M17").Select
End Sub
Here are the Dynamic Range Names (DRN) I'm using:
AnalysisTrim  =OFFSET(Analysis!$A$1,COUNTA(Analysis!$C:$C)+7,0,10000,250)
OperationsHeaders =OFFSET(Operations!$A$1,0,0,1,COUNTA(Operations!$1:$1))
OperationsOrders =OFFSET(Operations!$A$1,0,0,COUNTA(Operations!$A:$A),1)
OrdersData  =OFFSET(Orders!$A$1,1,0,COUNTA(Orders!$A:$A)-1,25)
SortCodeDestination =OFFSET(SortSheet!$A$1,1,0,COUNTA(SortSheet!$E:$E)-1,4)
SortData  =OFFSET(SortSheet!$A$1,1,0,COUNTA(SortSheet!$E:$E)-1,29)
SortOrders  =OFFSET(SortSheet!$E$1,1,0,COUNTA(SortSheet!$E:$E)-1,1)
SortResults  =OFFSET(SortSheet!$E$1,1,0,COUNTA(SortSheet!$E:$E)-1,25)
StagingCodeDestination =OFFSET(Staging!$Z$1,1,0,COUNTA(Staging!$A:$A)-1,COUNTA(Staging!$1:$1)-COLUMN(Staging!$Y$1))
tblOperations  =OFFSET(Operations!$A$1,0,0,COUNTA(Operations!$A:$A),COUNTA(Operations!$1:$1))
tblOrders  =OFFSET(Orders!$A$1,0,0,COUNTA(Orders!$A:$A),COUNTA(Orders!$1:$1))
tblSort   =OFFSET(SortSheet!$A$1,0,0,COUNTA(SortSheet!$E:$E),COUNTA(SortSheet!$1:$1))
tblStaging  =OFFSET(Staging!$A$1,0,0,COUNTA(Staging!$A:$A),COUNTA(Staging!$1:$1))
- - - - - - - - - - - - -
I don't know if it is the DRN that's causing the error, but I think I need to use one since the size of data block will change from run to run.
Any suggestions?

I'm trying to use the following formula:

HYPERLINK(CONCATENATE((CELL("FILENAME",INDIRECT(CONCATENATE("SHEET",E47,"!A1")))),"!A1"),INDIRECT(CO NCATENATE("SHEET",E47,"!$C$4")))

I'm trying to read the value in E47 which is a number from 1 - 100 and build the Sheet100!A1 reference... IE... When I click on the hyperlink I want it to go to the sheet in the E47 cell in and land in cell A1 of that sheet. I also want it to read what is in C4 of that sheet and make it print that as the hyperlink text. I can get it to display the text without a problem but when I try to go to the hyperlink I get: Cannot open the specified file.

The reference is within the current workbook and I tried it without the "CELL" formula and couldn't get it to work that way either.

Anyone got any bright ideas that might help me out?

I have this error on one of my worksheets........

A formula in this worksheet contains one or more invalid references.

How do i figure out what the problem is and correct it.

I apologize if this question exists somewhere else. I tried searching, but I have not found anything. I don't even know the best way to ask this. However, here goes...

I have a spreadsheet that has 28 columns that I am interested in manipulating. There are actually more columns, but the 28 are the ones that I am letting users change and also using for printing. What I would like to do is create a private function that basically holds the number of columns that I am interested in. This way, if it changes, I only need to make one change, rather than changing every reference to the 28. My function is simple and looks something like this...
Private Function NumbColumns()
NumbColumns = 28
End Function

I would then like to use that in another macro when trying to select data. For example, I am currently situated on the last row of data that I care about. I want to select row 1, column 1 through the row that I am on and the number of columns that is stored in NumbColumns. I was trying the following, but I keep getting an invalid reference error...

Application.Goto Reference:="r1c1:R[0]C[NumbColumns()]"

Is this possible? Is there a better way of doing this?

Thanks in advance for any help.

I have this workbook containing one sheet of data and 32 charts referencing that data. There's a macro in there for clearing out some data, but you can just refuse it permission to run. I believe the problem is independent of the macro.

The 2nd set of charts (Ch1Val, Ch2Val, etc) uses dynamic ranges, e.g. ranges defined by formula instead of by reference. For example the range "Ch1Prog" is defined by the formula:

This permits me to graph a data set between two values that I want, even if I change the number of data points in the set.

The problem is that I have a macro that clears the data set before I load the data from another piece of software. When I clear A9:D329 those dynamic references are invalid because the lookup functions all return #N/A. If you clear those cells and then click on Ch1Val, Excel will throw up the error, "Invalid reference . . ." No problem there; that's expected behavior.

But when I load new, valid data, that error should disappear. If you copy cells T9:W329 back into A9:D329, those references are now valid again, and everything should work. But if you click on Ch1Val, you still get the same error.

Note that in 2007 this error only re-appears on charts that have already displayed that error. If you click on Ch2Val, there will be no error. But if you were to have clicked on Ch2Val while the refs were invalid, then Ch2Val _will_ display the error the second time. In 2010 it appears regardless of whether anything was clicked after the data was cleared.

If I just save and reopen the workbook, then there are no errors, and the charts are correct. So there's nothing actually wrong with the workbook. It's just a problem with Excel getting into an invalid state.

If I were to guess, Excel somehow marks those charts as "invalid" the first time, and then doesn't properly clear the error when the refs become valid.

This did _not_ happen in Excel 2000 (which we've been using forever, but can no longer use), but it happens in both Excel 2007 and Excel 2010. In 2007 it happens very reliably. In 2010 it happens only if I run the macro that clears the named range "Measurements" and then save, then load the "new" data.

This is a crippling bug for us, because it interferes with an automated workflow, so I have your choice of a premium six pack or a dozen donuts to anyone who can find me a workaround!

Thanks!

Hi Folks,

I need to delete some tabs from a worksheet which contain no data. However, numberous cells in my workpaper link to these tabs. Although this does not result in any change in value, since the cells are blank, deleting the tab will result in #ref errors.

Is there a way to delete a tab and also remove all reference to said tab? The tabs serve no puprose and it is bothersome that removing them would result in a lot of wasted time removing the invalid references. Any help you can offer is greatly appreciated, thank you for your assistance.

I have 2 files called the same but in different folders. I need to reference cells from the other workbook. However, I get an error message ('A formula in this worksheet contains one or more invalid references').

I know it is not an error with the formula
.

Both files are called A&E.xls

Has anyone come across this before and has a solution?

Thanks
Jonathan

Hi,

I was wondering whether there was a way to return specific text if a value is not found in an array,

The formula that I'm currently using is:

=IF(ISERROR(VLOOKUP(C2,Y6:AC38,2,FALSE)),"",VLOOKUP(C2,Y6:AC38,2,FALSE))

So for example, if the value doesn't exsist in the table, is it possible to return "Invalid Reference Number".

Thanks in advance for the help.

Morning all,

The attached spreadsheet / graph is used on a daily basis, and has been for the last year or so without incident. However, since upgrade to 2007, excel reports an "Invaid Reference" error when the sheet is first opened. I have tries to find the error, but to no avail.

I'd appreciate any suggestions.

Thanks,

Dave

I have a script which creates 3 workbooks, puts relative data in the workbooks, pivots the data, and saves the files as .XLS. One of the reports keeps experiencing an issue where the pivot table is changed to flat data upon re-opening. I threw the data in a new workbook, ran the script, and when I save I receive an error saying Significant loss of functionality due to a Pivot Table exceeding former limits. The problem is that the Pivot Table doesn't exceed 2003 limits. The data reference for the Pivot Table is a named range which references $A$1:$AF$42. The only other thing worthy of note is that the source worksheet is hidden as part of the script. I would greatly appreciate some help because I am at a loss since the other 2 reports work just fine. Here is the code that creates the Pivot Table.

	VB:
	
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
"Evaluation", Version:=xlPivotTableVersion12).CreatePivotTable _ 
TableDestination:="Classroom_Evaluation_Trending!R1C1", TableName:= _ 
"PivotTable2", DefaultVersion:=xlPivotTableVersion12 
Sheets("Classroom_Evaluation_Trending").Select 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Site") 
    .Orientation = xlPageField 
    .Position = 1 
End With 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Trainer") 
    .Orientation = xlPageField 
    .Position = 1 
End With 
 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Line of Business") 
    .Orientation = xlRowField 
    .Position = 1 
End With 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("WeekEnding") 
    .Orientation = xlColumnField 
    .Position = 1 
End With 
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
"PivotTable2").PivotFields("In Class Score"), "In-Class Score", xlAverage 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("In-Class Score") 
    .NumberFormat = "0.0%" 
End With 
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
"PivotTable2").PivotFields("ABay Score"), "A-Bay Score", xlAverage 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("A-Bay Score") 
    .NumberFormat = "0.0%" 
End With 
With ActiveSheet.PivotTables("PivotTable2").DataPivotField 
    .Orientation = xlRowField 
    .Position = 2 
End With 
With ActiveSheet.PivotTables("PivotTable2") 
    .InGridDropZones = True 
    .RowAxisLayout xlTabularRow 
End With 
ActiveWorkbook.ShowPivotTableFieldList = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines