Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










SUMIF Doesn't Work From Other Workbook When Closed


I have a sumif formula in one workbook that relates to information off of another workbook...when the other workbook is open the information populates no problem when you close the other workbook I get the #VALUE error. Is this typical of SUMIF when referring to another workbook? I have a vlookup function that refers to the same workbook and it works fine when the other workbook is closed?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro Doesn't Work When Workbook/File Is Shared
I am encountering a specific scenerio where In I am creating a New file by copying one of the sheets And renaming that With todays date. here starts me problem when I try To share the sheet To work around I am seeing that th macro Is Not copying the sheet properly even though i have given PasteSpecial. The code goes here

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing Is Not transferred when copy/pasting, but Is If the keyword uses "quotes".
Sub Newsheet()
sheetname = Format(Now, "dd-mmm-yyyy")
MsgBox sheetname
sheet_count = Worksheets.Count
'Checking for Replication................

View Replies!   View Related
Doesn't Work If The Worksheet Hasn't Been Saved Yet
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000.

2 reasons I'm doing this:

1) It doesn't work if the worksheet hasn't been saved yet.
2) It's ugly

=DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0)

The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it)

Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.

View Replies!   View Related
Hyperlink Doesn't Work
I have a hyperlink to [url] that doesn't work. When I click it I get an error saying "Unable to locate the server or proxy server". But when I cut & paste the address from the hyperlink into my browser (no chance of mistyping), it works fine.

View Replies!   View Related
Right Click Doesn't Work
As said, the right click doesn't work anymore, last week it was working but it doesn't anymore. When I right-click wherever in the spreadsheet nothing happens but I know it's working because it works outside excel.

View Replies!   View Related
Some Times If I Do A =if( ) Formula It Doesn't Work
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?

Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.


View Replies!   View Related
Full Screen Doesn't Work.
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.

View Replies!   View Related
Programme Doesn't Work When Sent To A Different Computer
I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying.

The part of the programme I'm having difficulty with is this:

Range("SsNo1").Resize(d, f).Offset(a, 0).Select
Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

View Replies!   View Related
Why Doesn't My Variant Array Work
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time,

The code I have is:

Sub test()
Dim vSheetColours As Variant
Dim iCounter As Integer
vSheetColours = Range("Colours").Interior.ColorIndex
For iCounter = 1 To UBound(vSheetColours, 1)
MsgBox vSheetColours(iCounter, 1)
Next iCounter
End Sub

(Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!)

Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours.

If I run this code, I get a runtime error 13 type mismatch, and it highlights
For iCounter = 1 To UBound(vSheetColours, 1)

But if I replace
vSheetColours = Range("Colours").Interior.ColorIndex
with
vSheetColours = Range("Colours").Value

it works fine.

View Replies!   View Related
Application.ScreenUpdating Parameter Doesn't Work
I am trying to run a macro in the background. I have added the Application.ScreenUpdating = False (and True at the end) to my code but no luck.

View Replies!   View Related
Find Method Doesn't Work With Some Text
I have a spreadsheet with data similar to the following:

12111000 MILK, COW'S, FLUID, LOW PERCENT
27313010 BEEF, NOODLES & VEG (W/ CARROTS/DK GREEN), NO SAUCE
11100000 MILK, NFS
11111000 MILK, COW'S, FLUID, WHOLE

If I use the following code to pass in a string variable and then attempt to find the string it never sees the text in line 2; or any line that contains (). The code is simply a test to try and figure out what i'm doing wrong. The actual goal is to search the entire sheet for text similar to that entered in by the user and then copy every row that contains similar text into another worksheet.

Sub CopyStuff()
strVariable = "car" 'I have tried "*car*" also
With Worksheets(1). Range("b1:b500")
Set c = .Find(strVariable, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Set c = .FindNext(c)
MsgBox (test)
Loop While Not c Is Nothing And c.Address <> firstaddress
test = c.Address
End If
End With
End Sub

View Replies!   View Related
Sendkeys Doesn't Work On My Laptop With Vista
I have a workbook that I transfer back and forth from my desktop to my laptop, that uses Sendkeys to add code to the active pane in VBA. It doesn't work on my laptop, but works fine on my desktop.

Both computers have Vista. Is there a switch somewhere to make Sendkeys work?

View Replies!   View Related
Error Handling Doesn't Work Properly
I've written the following bit of code but the error handling doesn't work properly: ....

View Replies!   View Related
Copy And Paste Doesn't Work If Macro Is Running
I have my macro that every second does a copy and paste.
but window doesn't copy and paste anything else but the macro's value
is it normal?

View Replies!   View Related
LARGE() Doesn't Work When Having Multiple Max Values
I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule.

Example:
In this row:
7
7
6
5
5
4

I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7.

How do I get the second value that I want?

Maybe if I use the RANK() formula in some way, but how?

View Replies!   View Related
2003 To 2007, VBA Codes Doesn't Work
I always used Office 2003 .. I just installed Office 2007 to try it out. But everything doesn't work in Excel 2007, which does work in Excel 2003.

I added an example of the list, but here are the codes:
Sheet1

View Replies!   View Related
Insert Picture Macro Doesn't Work In 2007
I just upgraded to Excel 2007 and my macro from Excel 2003 for inserting a picture doesn't work correctly in 2007. I have a command button that states insert picture and when you click it, it will let you insert a picture into the cell and hide the command button. In 2007 it will let you insert the picture, but it isn't centered and expands into other cells and the command button is still visible. The picture appears to be the correct size it just doesn't center itself in the cell and the command button is still visible. Below is the

View Replies!   View Related
Formula Requires Parameter In Quotes, Indirect Doesn't Work
I've been given a formula to use (embedded in an add-in that is password-protected). One of the parameters REQUIRES quotes.

The formula goes like this: =MYFORMULA(1,2,3,"ABC")

If I put ABC in a cell (say C5) and use the formula =MYFORMULA(1,2,3,INDIRECT(C5)), the formula doesn't work.

If I put "ABC" in cell C5, the indirect function still work make the function work.

Even if I have ABC in cell C5 and use =MYFORMULA(1,2,3,""""&C5&"""") or =MYFORMULA(1,2,3,""""&INDIRECT(C5)&""""), these won't work.

I wish I could just change the UDF behind this, but that isn't possible.


View Replies!   View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.

For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.

My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.

View Replies!   View Related
This Simple 10 Line Code Doesn't Work If Just The Reference Is Changed..
I asked for a macro to delete the whole row if a duplicate customer number was found in column B. Sometimes, though, my column numbers change. So, logically thinking, I simply changed the criteria, but the macro ONLY seems to work if duplicate customer numbers are in column B only.

This code below won't work if the Customer Number is in column D instead of B even if the reference of B:B is changed to D:D, it doesn't carry the macro over.?

View Replies!   View Related
Conditional Formatting For Row Banding Doesn't Work After Autofilter
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.

View Replies!   View Related
VBA Code Doesn't Work When Formula In Sheet Is Removed
I had to remove a lot of pictures in the xls file in order to attach it, (since the original is about 5.3mb) but it worked out. When you move your arrow over the titles in column A a picture shows up thanks to a code. When you change the status in column G, the color changes together with the title in column A, as it should.

View Replies!   View Related
SumIF Returning #VALUE! When Referenced Workbook Is Closed
My SUMIF function only works when the workbook that I'm referencing to is open. When it's closed and I open the file it shows #VALUE!. How do I fix this problem?

View Replies!   View Related
Macro To Copy Over And Paste Data In To A Work Sheet From A Closed Workbook
I have a macro code which gets the file list from a folder i specify and puts it in to an excel sheet as a column. I have then made a drop down list from this so the user can select the file they want.

From this file i wish to copy the data on a sheet that i specify. For example the sheet "dump" from file FR7_19.11.2009.xls (which will be a closed workbook) and paste its content in to the sheet "dump" in Summary.xls

I have a macro which opens up the closed workbook FR7_19.11.2009.xls and copies the sheet "dump" and then creates a new sheet of the same name and content in my current workbook (Summary.xls). However when i wish to select a different file to load in to Summary.xls "dump" the formulas i have been calculating information from this sheet all come up with #!Ref errors. I know this is because the macro i use deletes the old dump sheet before re adding a new one containing new data.

I am therefore looking for a macro which will simply just copy and paste the data from any file i select in to a sheet named "dump" as the data is always set out the same in every file but the values are different. I assume this will then mean that any formulas i use relating to this "dump" sheet in Summary.xls will work because the sheet is no longer being deleted and re-added the data within it has just simply been copied over.

View Replies!   View Related
Macro Doesn't Work On Button, But Fine From Macro Menu!
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

View Replies!   View Related
Match(NamedRange,NamedRange,0) Doesn't Work
I have an array formula: {=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($C2)+1),1,0))}

where Group is: OFFSET(Groups!$C$2,0,0,Groups!$D$1)

and the array formula is entered in Groups!E1 and returns an #N/A error.

If I select the formula in the Formula bar and press F9 it evaluates to 48 which is the result I am looking for.

If it calculates OK using F9, why can't excel resolve it in the cell??

When I enter the match part as an array {=MATCH(Group,Group,0)} in an appropriately sized range, it works fine and returns the index of the first occurrence of the elements of the Groups array as it should.

The Groups array in column C is an array formula entered over 388 rows and contains:
{=IF($A2:$A389="","",INDEX(XCorel!$IC$2:$IC$232,$A2:$A389))}
where XCorel is another sheet in the same workbook.

Can anyone suggest why this might be?
If I replace the array formula in column C (Group) with its values the problem goes away, but I want it to be dynamic.

View Replies!   View Related
Save Workbook To Desktop If File Doesn't Exists
I'm having a slight problem with this script. What I'm looking to do is before the workbook is closed is check to see if the workbook already exisits on the users desktop if it doesnt then save it to the users desktop. if is does exists then just exit the sub. Here is what I have so far but for some reason it kept displaying the aleart message that the file already exists and wants to save it again.

I just added the displayalerts = false in but the script still does not know that file already exists. So when the file is closed it keeps saying file has been placed on your desktop.

View Replies!   View Related
Copy Between Worksheets Doesn't Work (look For A Certain Value In Worksheet A And Copy That Row Of Data To Worksheet B)
look for a certain value in worksheet A and copy that row of data to Worksheet B.

However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out?

PHP Private Sub GetInfo_Click()
    Dim r As Long, LastRow As Long, Status As Integer
    Dim Message As String, Title As String, Default As String, MyValue As String
    Application.ScreenUpdating = False
    
    MyValue = Range("A4").Value
    Workbooks("invoice.xls").Worksheets("A").Activate
    LastRow = Range("C65536").End(xlUp).Row
    For r = LastRow To 1 Step -1
        If Cells(r, 1).Value = MyValue Then
            Rows(r).EntireRow.Copy
            Workbooks("invoice.xls").Worksheets("B").Activate
            Rows("8").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Status = 1
            Workbooks("invoice.xls").Worksheets("A").Activate
            Rows(r).EntireRow.Delete
            
            Exit For
        End If
    Next r
    Application.ScreenUpdating = True 

View Replies!   View Related
Run-time Erro '445' : Object Doesn't Support This Action
While clicking a button in the excel, am getting a run-time error. Everybody's need is urgent even I understand boss. Anybody else who is in the same shoes as mine. Here goes the solution:

View Replies!   View Related
If Statement: =IF(A2=A2,"CN","US") Doesn't Work
=IF(A2=A2,"CN","US") doesn't work


ie

**
US
US
**

with give me this

** CN
US US
US US
** CN

View Replies!   View Related
Copy Range From Closed File & Paste To Closed Workbook
Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder

View Replies!   View Related
Count Closed Work Orders
I need to count the amount of work orders that my employees have completed.

Review example.
How many work orders did chuck complete? (Column D)
How many work orders did brian complete?

View Replies!   View Related
Sumif For Values From Closed File
I am trying to summarise data from 50 different files (updated by 50 different users) into one file in one place. To do this I created a simple sumif function that links to those files. This sumif works correctly when those files are open. But when I open my output file after changes have been made to one or more of 50 input files, sumif function doesn't work when I hit update at the file opening pop up question and get #value error. If I start opening those files this error goes away and it works great again.

View Replies!   View Related
#VALUE Error When Using Sumif On Closed Workbooks
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??

View Replies!   View Related
Auto Fill Doesn’t Work, But Trying To Avoid Manual Entry Of Rows.
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)

Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….

=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!

However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?

To put this formula…
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?

View Replies!   View Related
Run A Macro Automaticly On A Closed Work Book Every Friday
Is there a way to run a macro automaticly on a closed work book every Friday ? I've found ways of doing this using on-open event but It made me wonder if this was possible.


View Replies!   View Related
How Do I Check Cells In Multiple Work Sheets With SUMIF
How do I get a function to check cells on multiple work sheets.

For example this function searches for the word "hello" in cells, A1 to A50 and then adds up the number in the corresponding cells where "hello" is found from C1 to C50:

=SUMIF($A$1:$A$50,"=hello",$C$1:$C$50)

Two questions:

01) How do I search the same cells in two further work sheet, "Sheet2" & "Sheet3"?

02) Is there a way to search every cell in an entire work sheet?

View Replies!   View Related
If Cell Doesn't Have Formula
I need a macro using worksheet activate event that will look a cell a10
and select if it doesn't have a formula

View Replies!   View Related
Validation Drop Down Doesn't Appear
I named a range State, it contains a list of all the US state abbreviations. I do a data validation that refers to this range as a List. If I type in something that's not in the named range I get the error message I created for the Validation.

But - I don't get a drop down button. I tried to do the same validation on another cell and get the same thing. Other drop downs for validation appear.

View Replies!   View Related
Userform Doesn't Unload
I have a userform that after some operations won't unload. It executes subs from other buttons on it but it won't unload. I use ScreenUpdating but I always set it to true.

View Replies!   View Related
Listbox Doesn't Redraw
I have a worksheet with a listbox from the controls toolbox. I have to use the controls toolbox activex version because the box is formatted and I don't want a vertical scrollbar. I don't want to use a userform either because that would be major overkill.

When the view is set to full screen the listbox partially disappears. Paging up and paging down will redraw the screen and the listbox.

I've tried application.screenupdating with different listbox events but nothing works.

View Replies!   View Related
Transfer Data From Open Workbook To Closed Workbook
im looking for some code to transfer cells a3,d6,f9,i6,k10 and i18 from open workbook named "hello"

to

closed workbook named "goodbye" and input to next available row
a3 to a1
d6 to a2
f9 to a3
i6 to a4
k10 to a5
i18 to a6

View Replies!   View Related
Why Doesn't The Sound Play When The Value Changes Indirectly?
This is a complete mystery to me. I have some code (reproduced below) that plays a .wav file when any of the values in a1:a9 changes from 0 to greater than 0. This code works when I manually change one of the values to a trigger number in any of the cells.

The problem is that the values in a1:a9 are really pointers to other cells. For example,
a1 is "=sum(D9:D20)"

So, when I d9:d20 chnages values such that a1 changes from 0 to 5, the sound doesn't play. To put it simply, if I type "5" in a1 the sound plays. If a1 = D1 and I type "5" in D1 the value in A1 changes to 5 but the sound doesn't play.

How do I modify this code such that any change in the value of A1:A9, no matter the source of that data change, triggers the playing of the sound? ......

View Replies!   View Related
NAME Doesn't Stay Attached To The Cell
I have a cell (H27) which has a (sum) total in and have attached a NAME FRED.
This is fine as long as the client doesn't add a row above it.
If they do when I re-open the sheet the NAME doesn't stay attached to the cell (now H28)

Is there a way to keep the NAME attached to the cell regardless where it is?


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("$H$27").Name = "Fred"
End Sub


View Replies!   View Related
If Statement Doesn't Recognize Non-zero Values
I have the following formula with multiple if statements:

IF(N2>0,N2,IF(O2>0,O2,IF(P2>0,P2,IF(Q2>0,Q2,IF(R2>0,R2,IF(S2>0,S2,IF(T2>0,T2)))))))

This formula refers to cells in columns N,O,P,Q,R,S and T. Every cell in columns N,O,P,Q,R,S and T also contains formulas (VLOOKUPs).
My if statement works fine but when it checks cells in column P, for some reason, it doesn’t recognize cells with non-zero values. I can’t figure out why this is happening and how to fix this problem.

View Replies!   View Related
Isolate Data That Doesn't Match
I have a long lists of values (product numbers) from our inventory records. Now, we've been given a new list of product numbers, some are the same and some are not.

I want to isolate (spit out) the values from column A, than don't match column B. Column B is the list we SHOULD be using, column A is what we're currently using. See example below.

Old Products New Products
34544 34544
34545 34546
34546 34547
34547 34548
34548 34550
34549 34551

View Replies!   View Related
VBA Doesn't Recognise Selection
I have a report that has been running in it's current state for several months with no issue. However, today it has decided that it doesn't understand the word 'selection'.

eg: selection.copy gives compile error.

Now... obviously NOT USING selection is preferred. However, let's put that aside for now because there are LOTS of these in this report and it's never had an issue until today.

Is there some setting that could have gotten changed or something?

It's happening on multiple computers and we're running Excel 2003 (standard) with SP3.

View Replies!   View Related
Delete Any Rows In Which Column B Doesn't Have ...
I'd like to tell Excel that anytime it finds the text "ABC" anywhere in any cell in column B but doesn't have any data in column A, to delete the entire row.

View Replies!   View Related
Counting Even If Doesn't Meet Criteria
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26.

Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field.

Here is the formula/function I'm using:

=(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")

View Replies!   View Related
Private Sub Workbook_Open() Doesn't Execute
why the code below might not execute ? It just seems like it skips over the code and doesn't run at all.

Private Sub Workbook_Open()
'AssignRequestNumber()
MsgBox ("Whoa")
Run "AssignTrackNumber"

End Sub

I have a macro assigning a unique number to the file in a fixed cell whenever the .xlt file is opened from File-New.

View Replies!   View Related
Code Doesn't Loop Properly **
I have some code (probably a little inefficient, but still) that should delete any rows that contain nothing in column V. My problem is that it only deletes 1 row at a time:

Dim c As Long
Dim Limit As Long
Limit = Cells(Rows.Count, 11).End(xlUp).Row
For c = 2 To Limit
If Cells(c, 22).Value = "" Then
Cells(c, 22).EntireRow.Delete xlUp
End If
Next c

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved