I am linking two worksheets together. Formulas work fine except when I close the source sheet, it turnes into #REF error. I saw in one of the posts that you can not use INDIRECT formula on closed workbooks (INDIRECT is a part of the formula in my sheet) and downloaded the changingExternal. zip file as directed. Although, after staring at it for a while, I realized that I had no idea as to what was going on in there.
View 3 Replies
I'm compiling statistics from a number of separate workbooks. When I (or more to the point - one of my staff) open the Stats Workbook the formulas show #VALUE unless the other Workbooks are opened. I want the Stats to calculate without the user having to open the other workbooks as most users won't know which ones to open.
I could generate VBA code when the workbook opens to also open the other ones but wondered if there was another solution.
I am using this formula in Product Balance Update.xls,Tsuchiura worksheet that update data from A-Tsuchiura.xls, INVOICE worksheet.
When both workbooks are is opened, all linking are fine.
But upon A-Tsuchiura.xls is closed, my linking in Product Balance Update.xls,Tsuchiura worksheet will shows #N/A.
I had tried many ways for sumproduct
1) =SUMPRODUCT(--('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3),--('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))
2) =SUMPRODUCT(('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3)*('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))
I had read through this forum that sumproduct should work in closed workbook but I still cant get it why this error ocurr..
Please refer to attached to have a clearer picture of error
As I am at work, it does not allow me to upload the file due to restrictions on uploading. However, the spreadsheet is fairly simple so should be able to describe it.
The main sheet is called "Investigation Court Apps". An entry is made into each row and the status shows as "Open" in Column A, until a final closure date is input in Column Z, at which point the entry in Column A changes to "Closed". This is achieved with the following IF statement in Column A.
=IF(B3="","",IF(ISNUMBER($Z3),"Closed","Open"))
So that part is all automated. What I need is some VBA coding or macro (might be same thing but completely new to all this) that when the value in Column A changes to "Closed" the whole row moves over to "Sheet3". Once the row has been moved over, I want to ensure there are no blank rows also.
I have tried the following amongst others:
Sub MoveToSheet3()
Dim C1 As Range
Dim RowNum As Integer
For Each C1 In Sheets("Investigations Court Apps").Range("N1:N" & Range("A65536").End(xlUp).Row)
If Cll = 0 Then
[Code]....
I know to open VBA its Alt+F11 and to close Alt+Q, but thats about it.
I'im using an index - match - match formula with links to an external file.
Everything works fine until I close the source workbook. At that moment the liks break. The error I'm getting is #REF!.
I suspect the reason is that the source data are in form of an official table. I tried some formulas with data in the same workbook but outside the table and they keep working after the source file is closed.
I really want to keep the source data in a form of an official excel table.
I have a pivot table in spreadsheet A. I have some formulas in another spreadsheet B. The formulas look up data in the pivot table in A and use that data in B to return and answer. Is there a way to refresh the pivot table in A from B without having to open A and refresh the table?
View 3 Replies View RelatedThere is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
If Range("a7").Value = "" Then
Range("A1:A6").Copy Destination:=Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll, Transpose:=True
'Range("C1").PasteSpecial xlPasteAll, Transpose:=True
End If
This command shows error?
I'm working on a function that gets its values from a Data Validation List.
My problem is that I want the function to return a value when an item in the list is selected and another if the user did not make a selection... just a basic if....then....else ... but the cell where I call this function shows the #VALUE! error while no item in the list is selected.
I call the function like this:
=myFunction(RC[-5], RC[-2])
where RC[-5] is the Data Validation List and RC[-2] is a cell where the user should enter a number
why I'm getting the #VALUE! error instead of my message
The function basically looks like this:
VB:
Range("a1:n" & row_end).AutoFilter 13, "ABC"
If the value "ABC" not found in table, it shows error msg, In my file data="ABC" avail, sometimes not.
I was given a workbook with several bugs and asked to fix it and make it functional again. I was able to fix everything except for a runtime error 1004. On the debug it highlights the following code:
Code:
Private Sub ComboBox1_Change()
End Sub
Private Sub Worksheet_Activate()
LoadSheetNames "Compare_Session_Start", "Engineer*"
End Sub
Private Sub LoadSheetNames(strRangeForValidation As String, strDesiredCodeName As String)
[code]....
Anytime you select the base worksheet or a duplicate it shows the runtime error...
I have this formula in a excel file called test.xls which does a SUMIF on file "Sales By Customer v3 17-11-08.xls". It works fine when I have Sales By Customer v3 17-11-08.xls open at the time. When I close it the file displays a #VALUE in the cells
My formula is:
=SUMIF('G:FINANCEAccantia Budget2009Sales and GC[Sales By Customer v3 17-11-08.xls]DP Total'!$B$7:$M$7,Cover!M$85,'G:FINANCEAccantia Budget2009Sales and GC[Sales By Customer v3 17-11-08.xls]DP Total'!B9:M9)-'Page 15'!E9
This is really annoying as the formula works, and you need to open the linked file to display the results, otherwise you get the #VALUE. Is there anyway around this and why is it doing it??
Column D = Dates to be used in each of the matches.
Cell C2 = data-validation cell, for choosing the specific Named Range that contains the data to be returned from the closed wb.
VBA code automatically opens the closed data file and then closes it after returning the data to the main file.
find the error in the cell formula (or named range formula) that’s preventing this from working correctly? (see atttached files)
I keep getting an error in this code while trying to write the results of a query to a sheet in the current workbook. The error i get is 'operation is not allowed when the object is closed'... since i didnt close the recordset before trying to write the results to the worksheet, im at a loss to explain why im erroring...
Public Sub fillWorksheetWithData(dataSheet As Worksheet, conStr As String, sqlStr As String)
'clear datasheet
LastRow = dataSheet.Range("A65536").End(xlUp).Row
If (LastRow < 2) Then
LastRow = 2
End If
dataSheet.Range("A2:A" & LastRow).EntireRow.ClearContents
I am automatically generating a hyperlink to another document. The link that generates is: (I don't know if this qualifies as code, but I'll tag it anyhow...)
06-09-05 0000020 Name/06-09-05 0000020 Name.xls# 'Notes'A5
with 06-09-05 0000020 Name as a directory off the current App.path, and 06-09-05 0000020 Name.xls being the name of the file in the directory. It does have a sheet called "Notes". The thing of it is, the hyperlink takes me to the right place, it just gives me an error message every time saying "Reference not valid". How can it be taking me to the right place, and still giving me the error?
I have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.
Here is the formula when the referenced file is open:
=VLOOKUP(A38,'[June Scorecard confirmation.xls]VPSC Summary'!$C$10:$F$22,4,FALSE)
The full & correct path appears in the formula when the referenced file is closed.
Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.
Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.
I have two worksheets that directly link a range of cells on both sheets, ie: Output!a1 = Database!a1.
In this case, the Database sheet contains data that I want to manipulate by deleting entries that are defined in rows. however, when I delete one of the source rows I get a #REF error on the Output sheet.
Is there any way to avoid this so the entries will automatically shift down a row if a row has been deleted? :o
So if i were to delete row two on the source page and the data from row three moves down, that would, in turn, cause the row three data on the output page to move down one row as well.
got this quite complciated formula.
any of the cells specified can be blank at start of spreadsheet
is there a way to hide the DIV/0 error from appearing
this is the formula:
=((((((SUM(C11:C14)*(100%-R2)+(SUM(ThroughputFormulas!C33:C36)*R2)*(L2/30))/1000)*N2)*3600)*P2))/(SUM(C39:C45))/1024/8
I am currently creating a form to hold 2 drop down lists that change depending on the selection in the first one. I had never done this before and taught myself and got it to work minus one problem. As soon as I input the formula under the Data Validation the error: "The Source currently evaluates to an error. Do you want to continue?" pops up. Right now the formula works except for the last 6 options in my first drop down. When I select them nothing pops up in the 2nd drop down menu. I have double checked and triple checked that I have no spaces or anything in my LIST on worksheet 2 or any of the names are input correctly.
A7 is the first drop down - PROPOSED
B7 the second. - ITEM #
Lists and data to populate the lists are on worksheet 2 with the "List" at the bottom of worksheet 2...
why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.
Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. The workbook in question is attached.
I'm trying to create a userform with a combobox wherein items that will be provided in it will be based on the worksheet named NA. When i try to change the Rowsource on the properties of the combobox i'm getting an error "could not set the rowsource property. Invalid property value". Do I need to declare the name of the worksheet before changing the rowsource?
View 6 Replies View RelatedI already have a conditional format which is working OK. I would like to put a formula in the cell so it shows a % so currently it would be 100% as there is 3 cells below showing 3 greens.
Alternativily each colour is worth a value - Green = 1, Amber = 2, Red = 3. So Cell I3 would show 3.
I am trying to create a basic invoice spreadsheet.
On worksheet 1, I have days of the month A4 to A35. Columns B, C, D, E are headed.
Every hour of work I would enter a " 1 " in the correct cell for the day. For example, 1 hour, every segment of the day, I would enter "1" in cells Bx, Cx, Dx and Ex. This continues for the month;
On a 2nd worksheet, this is neatened up, and I have added formulas to work out the cost and type of call;
On the third worksheet, I want to display roughly the same, however when there has been no work on the day (ie the date displays "None" and the adjacent cells show: blank, blank, blank, blank, £0.00), I want it to skip the cell and display the next date which has data to show (ie a date). The data on the adjacent cells needs match with the date (in the same way as physically deleting a 'non worked' whole row).
This then on the invoice worksheet would look neat and display only the days actually worked.
What formula(s) could I use to do this.
Hi i have the following SUMPRODUCT formula which works as required, but when i clear the raw data and apply new raw data and apply macro that sorts my data, then all the ranges in the formula shows #REF!
=SUMPRODUCT((Raw_Data!C6:C5000=Summary!$B10)*(Raw_Data!A6:A5000=Summary!C$8)*(Raw_Data!E6:E5000>2))
What is causing this to error. Basically I'm just trying to highlight cells, and create a pivot table out of that. Here's my Code:
Sub Macro1()
'
' Macro1 Macro
Dim myRange As String
myRange = "Sheet1" & Selection.Address
Sheets.Add
[Code] ..........
When I debug, the value of myRange is something like Sheet1$A$19:$K$37 with the actual cell values being whatever I highlighted. I get a Runtime Error 5 Invalid procedure call or argument. I'm assuming it's something with my sourcedata variable, but I'm not sure what.
Also, I did try commenting out the Sheet.Add and Sheets("Sheet2").Select Cells(3, 1).Select lines, but it still produced the same results.
I am facing an issue related to IF formula. It does show the correct result in one and another cell, but in a few others - doesn't.
This is strange since all variables are correct, and parameters are the same for every cell. And, in theory every cell should show the right answer.
In addition, the result doesn't change even when changing variables in cells that are dependants to cell where IF is located.
Concerning the formula itself. Variables are as such:
- different container types (20, 40, 40rf, 45cs, 45rf, 45)
- different weight limitations per container type (e.g. 20 container max weight 26t)
- using IF(OR(AND(....
- if any of the conditions met (e.g. 20 container < 26t ) then VLOOKUP for the rate from data based on the place name.
- if non of conditions met, then show "Overweight cargo"
I have following formula:
Why does this formula show result as "FALSE"?
If cell B56 is empty, then I need a result of "". i.e. blank result, but it shows FALSE word.
IF(LEFT(B56,2)="on",
then you go straight to the TRUE nested IFs
IF(LEFT(B56,2)="on",
IF(LEFT(B$52,3)="Pla",RIGHT(Table!AN4,2)&"% Discount",
IF(LEFT(B$52,3)="Gol",RIGHT(Table!AO4,2)&"% Discount",
IF(LEFT(B$52,3)="Sil",RIGHT(Table!AP4,2)&"% Discount",""))), "need something here" )
so to get ""
IF(LEFT(B56,2)="on",IF(LEFT(B$52,3)="Pla",RIGHT(Table!AN4,2)&"% Discount",IF(LEFT(B$52,3)="Gol",
RIGHT(Table!AO4,2)&"% Discount",IF(LEFT(B$52,3)="Sil",RIGHT(Table!AP4,2)&"% Discount",""))), "" )
I am getting 0 or 12:00 AM when I format cell as time and put formula.
I working on timesheet using this formula (=SUMIF('2'!G4,"="&TODAY(),'2'!E17)). If date in G4 on sheet2 match with todays/current date then copy data in cell E17 to sheet3(b11).
G4 = todays date
E17 = time eg. 2:25 AM ( I have to format destination cell as time because E17 has time value
Formula works fine but when there is no data in E17 or E17 is blank then my destination cell shows 12:00 AM.
I have an excel file which contains a full consolidated list of data (Master Tracker). I am now creating a new tracker and what i want to happen is for it to show the list of names from the Master Tracker which exceeded the current date "now()" and the status is still pending.
FYI: The master tracker and New tracker is different excel files Here is an example:
Master Tracker:
client
Candidate
Case Date
[Code]....
On cell K7, i have this formula:
=IF(ISERROR(I7-J7),"",I7-J7)
On cell L7 i have this formula:
=IF(ISERROR(RANK(K7,$K$7:$K$29,0)),"",(RANK(K7,$K$7:$K$29,0)))
The issue is that in cell K7 a dash (-) shows up and ranks this as the number one, even when i have no data in I7 and J7.
MTD Conv
Conv Goal
B/W Goal
RK
-
1
29.3
31
(1.70)
12
21.8
25.0
(3.20)
15
