I have the following formula in my workbook:
=COUNTIF('\caltulfs1DATAProjectsF2014FB-979916 - EQUATE7.0 DOCUMENTATION7.01 DOCUMENT REQUIREMENTS & RECORDS[FB-979916 DSI FORM.xlsm]Customer Tracking'!A12:A86,"past due")
When I open the master file I want my links to automatically update and open the referenced files without having to open the source document for each value. Can this be done
Is there a way to specify the filename for an external workbook that can be referenced throughout the spreadsheet? In my case it would be better if I could change the single reference instead of having to change it throughout the entire spreadsheet everytime I needed to use data from a different file.
View 2 Replies View RelatedI've got a field where I enter the current month, from this I derive a file path which is stored in A4.
ie: A1 = '01/11/2011' (UK Date format)
A4 = '\myserverdirectory20111111MyFile.xls'
What I'm trying to achieve is to then have a link in cell A6 that will link to the file in A4. I can specify my cell reference as this will never change, just the file path.
So, if I changed A1 to '01/12/2011' my A4 changes to '\myserverdirectory20111211MyFile.xls'
I'm trying to set A6 to something like ="&$A$A4&"!B1 to get the value of B1 on my referenced sheet.
I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?
In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...
Mary
='X:Anderson CorporationEmployees[Work Hours]Mary'!$F$42
Joe
='X:Anderson CorporationEmployees[Work Hours]Joe'!$F$42
Frank
='X:Anderson CorporationEmployees[Work Hours]Frank'!$F$42
Michelle
='X:Anderson CorporationEmployees[Work Hours]Michelle'!$F$42
Sam
='X:Anderson CorporationEmployees[Work Hours]Sam'!$F$42
i'm trying to do a COUNTIF from an open workbook. The range I want for it is in another workbook, which I do not want to have open every time I run the macro.
This is my current
Sub cellLink()
Sheets("Calls In-Out Trend").Range("ag18").Formula = _
"=COUNTIF('C:***[*.xls]sheet name'I:I, QXO)"
End Sub
editthe range is just so I can see if it's giving the right values, the range won't be like that once I get it working. I'll probably be using logic statements to place them in the appropriate cells. The * is just me taking out the directory names, its on the desktop)
It can copy cell values from another unopened workbook, but when I try to put in COUNTIF it doesn't want to compile/run the script.
That's basically the question. Here's some background and my aim:
I'm scanning row I:I for certain keywords which I then count up and put into corresponding columns of a chart.
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
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 document (unfortunately I cannot attach it) in Excel 365 that each time I open it I get the usual prompt that it is calling an external link. As much as i'd love to hide the popup I need to find the link to solve to problem but I'm at a loss.
Simply breaking the links doesn't work for some reason, so I've tried updating the source to reflect the current file. No luck there.
I've searched the workbook for the name of the link in question, searching for all formula for name itself as well as variants of "[" and ".xlms".
I don't see anything in the name manager referencing that external file.
I don't recall if I copied from that particular document, I may have as it was a duplicate file. I tried looking over the cells I believe I copied from it but didn't see the reference.
I've installed Kutools and Bill Manville (MS MVP) FindLink Tool. Both of which says there is no external link.
I've looked at the compatibility checker which basically just says, yes there is an external link but give no insight on how to address.
I've tried a couple of macros, some of which crashed excel, some of which simply said that there was an external link but not how to find it. In my frustration I forgot which one that was but I'm about to try that approach again and look for that code.
external links.png
How do I get rid of the link??? My formula seems like it is not working because it has a link which I cannot find anywhere.
dog.xlsx
Screen shot 2014-01-05 at 2.38.08 AM.jpg
I have the a COUNTIF function used on one workbook which refers to another workbook, however I get the result #value! unless the other workbook is open - this is even if I chose to update links when I fist open the file.
Do all workbooks have to be open when using COUNTIF?
If I open the other workbook after my workbook with the COUNTIFs on has been opened then all #value! errors disappear and the correwct info is shown.
Currently I have a database in an Excel template. After a user creates a new workbook from the template, a macro button on sheet 1 brings up a window (a userform) to allow selection of items from the database sheet. The item selected is entered on sheet 1 by means of VLookup. That works fine, but to edit the database correctly it is necessary to open the template itself. This is not a user-friendly method.
I figure the best way to solve this dilemma is to separate the template from the database. That is, make the database sheet into a workbook. This I've done. Here's the problem: What code is there that links the list box in the userform (of the template) with the closed database workbook? Is it even possible? If a file path type code is required, it may work on my computer, but when I use my template and database on another computer, the code doesn't work.
Another problem, and similar is this: I'd like to be able to have a macro button that opens the database from the new workbook (which was created from the template) in order to edit the database. If having a template seems to be my problem, I must have it since each workbook created from it is a different project.
I have a list of objects in column A
I want to select that list or part of it then run a VB module that references each item in the selection and compares it to a master list in a separate workbook that contains both the object and an additional value, and display that additional value in column B of the original workbook against each object in column A
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
I'm having trouble working this around, i use the following code in one of my Workbook sheets.
View 10 Replies View RelatedI am designing a spreadsheet at work, and need a little assitance. I can't find the answer I need online or here either. Here's the problem: I want to be able to enter data (A 5 digit job number, 80227 for example) into a cell, and then multiple other cells would auto fill with data from outside worksheets. The worksheets are in a seperate folder on my server and named under the same name as the project. (So if I entered 80227 as the data in A1, I would want A2, A3 and A4 to open spreadsheet 80227.xls on the server, locate the referenced cell/s, and insert the info from 80227 into them.)
I can easily reference the cells alone and have them input the data, but this is extremely time consuming since this spreadsheet has all the current jobs our company is working on. I currently am running a macro/VBA that automatically updates all linked spreadsheets without them having to be open, so that is not an issue.
I am writing a formula in cell "B1" in "December 2007 REVISED BY DAN.xlsm" that will reference cell "X1" in a workbook that has not been created yet.
The way I will tell the formula what the name of the workbook will be is by referencing cell "A1" and adding the extension ".xlsx" to it.
Example: ...
how to go about or refer me to how exactly you assign a closed workbook to be active. I know this sounds off and its probably the wrong way to word it so here's my problem. I am developing a macro in Visual Basic 2005 using VB. I have a connection string that looks like this:
Private m_sConn4 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:2005.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
What I need to do is connect to it and update which I have done successfully, then save it as 2006.xls. Is there some sort of m_sConn4.SaveAs or something of that nature.
Following syntax is incorrect, what is wrong? This should happen: Select op a specific worksheet (named DATA) in the workbook Report.xls and I empty the contents
Then I try with a 'With' statement to read the contents of a file named "sourcedata.xls' with helds a worksheet named "source". The values in this sheet should be tranfered to the sheet DATA in the workbook "Report.xls"
Sub FetchDataFromClosedWorkbook()
Dim FileName As String
Dim SheetName As String
Dim cellRange As String
Const ReportPath = "C:ExcelReports"
Windows("Report.xls").Activate
Sheets("DATA").Select
Cells.Select....................
I have a problem using COUNTIF function. I want to count cells say in range A1:A10 that are smaller that a value in a cell let's say B1. Neither of these works:
=COUNTIF(A1:A10; "<B1")
=COUNTIF(A1:A10; <B1)
How to do it? I saw your answer of Dave to a similar problem, but I still do not understand it.......
I am trying to use COUNTIF function to retrieve data to a "Report" tab on my workbook.I need it to be count if as I have 2 criteria whether or not the value in the cell should be counted toward the sum (one is text basically saying "Y" and other the date). Now the formula worked fine when use hard data such as this:
=COUNTIFS('Inspection Report'!$B$3:$B$1048576,"*" & "06/05/2014" & "*",'Inspection Report'!E3:E1048576,"Y")
OUTCOME: 47 (which is correct)
See how I used the date there, I typed it myself. However the end user should not be able to have access to the formula, so I used a cell with drop down list and the date is stored there. So following the logic, my new formula should be this:
=COUNTIFS('Inspection Report'!$B$3:$B$1048576,"*" & C2 & "*",'Inspection Report'!E3:E1048576,"Y")
OUTCOME: 0 (which is incorrect)
how the date is stored. By following my logic this should work as the cell only holds a string value and should be used like this in to calculate the result. if I need to use only the value of the cell in a formula like this should there be any conversion so excel understands what I am trying to do?
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 2 Replies View RelatedMy raw data is exported in CSV format. Is it possible for Excel to link to the data or can it only link to an Excel format workbook?
View 1 Replies View RelatedI have a list of URLs and I wrote a small VBA code to launch them when the user clicks on a button, however, it launches in Internet Explorer. Is there any way to get it to launch in Firefox instead?
View 2 Replies View RelatedI have a workbook which contains countifs and sumifs reading from about 10 different workbooks.
WHen I have the 10 files open, the values appear but when I close the 10 files, I get the 'VALUE' error. How I can keep the value amounts when the workbooks are closed?
I have all documents in the same folder?
Due to the large number of VLOOKUP functions on my page of data whenever I try to save I get the following error codes. Excel cannot complete this task with available resources. Choose less data or close other applications. Unable to save external link values. The problem itself may not be solvable but is there away to prevent these messages from coming up when I save (or when it autosaves?)?
View 2 Replies View RelatedWe're using SUMIF and it won't work unless the external excel file is open.
This is the formula we're using:
My Example
I have Master Book1 which has external links to Book2, Book3 and Book4.
My Formula in Cell B2 is ='D: empxl[Book2.xls]Sheet1'!$A$1
In Cells A1, A2 and A3 I have the text entries 2, 3 and 4 respectively.
Is it possible to use these Text entries to update the Formula I plan to copy down from B2 onwards?
i.e. Formula would read something like ='D: empxl[ Book"A1".xls]Sheet1'!$A$1
In an Excel sheet i have set up a link to an Access database query (select query) using the Import External Data method. The data is store sales by week, arranged in a pivot table layout (stores as rows, weeks as columns). As time goes by, the database will be updated for new weeks sales, and i would like to refresh the data and the new weeks automatically appear in the Excel sheet, in subsequent columns.
At the moment, the only way i can seem to get this to work is to open up the link in MSQuery and manually select the new weeks from the Access query. i have set the parameters to overwrite cells with new data rather than insert new data. i'm sure there is a way to do this, as it feels like it would be a common thing to want, but i can't seem to find an answer anywhere!
I want to be able to get a value from a specific cell in another workbook, the problem i have is that i want to be able to select what file the active workbook looks for from the start page of my current workbook. For example when i load the workbook i get my startup page ( sheet 1) where i will be able to select what external workbook the active workbook will be linking with, i will then link the active workbook to the value in a specific cell from the selected external workbook.
I know to link to another workbook i would use ='[filename]sheet!cell
However i want to be able pull up a 'open file dialog box' which i select a file name which will be used as 'filename' in the formula above.
I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:
='F:Projects[CustomerDB.xls]Sheet1'!VariableCellGoesHere
I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:
='F:Projects[ProjectDB.xls]Sheet1'!$A$355
='F:Projects[ProjectDB.xls]Sheet1'!$B$355
='F:Projects[ProjectDB.xls]Sheet1'!$C$355
Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?