I have an excel workbook we use for quoting jobs. All our pricing references are in another workbook on our network. I have reference in the 'quoting' workbook that refer to the 'pricing' workbook.
The trouble is, when some users open the 'quoting' workbook, and have another Excel file open, some references to the 'pricing' workbook change to the other file they have open.
I suppose there is some sort of replicable condition that is happening, but I can't figure out what is going on or why.
So my question is: Can I ' lock' an in-cell reference to an external workbook so that it NEVER changes?
I tried hand entering the reference (network location+workbookname+sheetname) into a cell and then referencing that cell, but I couldn't get that to work.
I am trying to use a conditional sum formula where the data resides in and external workbook. This is fine until I close the external workbook and the formula no longer shows the external reference. Is there a way of getting excel to use conditional sums in this way (or what is the best way of achieving this?)
I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).
In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.
BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.
I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.
Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.
One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?
I would like a defined name (1) in my excel document (A) to refer to another defined location (2) in an external document (B), which is a master document that will not move or be renamed. The main document (A) is one which will be copied to numerous locations within our company's network.
Therefore, I need the defined name (1) to have an absolute reference to the external document (B). At the moment I'm only able to get a relative reference, since Excel 'simplifies' the reference when both files are opened. Here is what I'm currently using: ='J:Invoice TemplateOffice list01 231106 List of offices.xls'!SiteNames
Is there a way of writing a formula to create a reference to an external spreadsheet? I have a folder containing 100s of workbooks all based upon a template. I can create a directory listing of this folder using a macro within Excel.
e.g. Directory listing C:My DocumentsReturnsFile1.xls C:My DocumentsReturnsFile2.xls C:My DocumentsReturnsFile3.xls
I would then like to use this list of file paths to create links to several different cells within worksheets contained in each of these. I know that if I did this manually for each worksheet the formula would be a variation on =[C:My DocumentsReturnsFile1.xls]Sheet!Cell. Is there a way that I can automatically create these external references? i.e using values in column A (directory listing) to create many external references, say in column B
I use my directory list and then a formula or macro populates automatically these external references? The file names in the directory change daily (100s of worksheets!) so I don't want to have to manually input these each time. If the folder containing the workbooks was held on an intranet would there be a similar solution?
Need to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).
Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?
All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?
I want to be able to import an external worksheet (say sheet1) to some data structure such as a 2d array or a dictionary. I don't want to store imported data temporarily in a local worksheet - I want to import external worksheet straight to array!
I have code that can import to a given worksheet name ... but I don't know how I can modify it such that it dumps it to an array or a dictionary.
The code I have is:
Sub read_in_workbook(sheet_name As String, source_dir As String, file_name As String, Optional append_from_y As Integer) Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset Dim stCon As String, stSQL As String Dim fsoObj As Scripting.FileSystemObject Dim fsoFolder As Scripting.Folder Set fsoObj = New Scripting.FileSystemObject Set fsoFolder = fsoObj.GetFolder(source_dir)
'###Instead of it dumping to a given sheet name, can I turn this routine to a function '###and have it return a populated array or dictionary object? End Sub
Every month I have to do a report that takes information from four other excel spreadsheets. The spreadsheets are all the same format and contain information on community events. I have to run a report that shows what events are happening in the following month. Currently I copy and past from all the relevant data from each spreadsheet into one. Is there and easy way of making all this information to come together every month.
I have code written to auto save the active worksheet when a button on my user form is pressed. What I would like to know is if there is a way to lock the worksheet when the button that triggers the save event is pressed behind the scenes so the end user has a more difficult time editing the sheet. I know they can just resave the document and the protection will be gone, but for minimizing user manipulation.
I need to lock a worksheet tab (Main menu) in place so that it is always visible. There are multiple tabs that are all linked to the main menu by hyperlink. When work is finished in a worksheet I want the main menu tab to be visible and usable rather than using the scroll functions to get back to the main menu.
I'd be very grateful if s.o. help me find solution for the following task:
I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.
What I'm usually doing and want to automate:
- I'm opening the first .tsv file from the monthly folder;
- Creating AutoFilter on the first row;
- Selecting a custom criteria from the AutoFilter;
- Copying the cells matching this AutoFilter criteria;
- Pasting in a predefined worksheet (with AutoFilter on first row);
*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.
- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).
So I am trying to create a budget worksheet for past and future expenses, basically an excel checkbook with running balance
In Column A I have my debits/credits
In Column B I have my running balance so that there is a current total next to each of the debit/credits in column A, it is formulated as follows, it adds the cells directly above it to the cell directly to the left of it (i.e. Cell B10 = B9+A10, which was dragged and iterated to many cells below)
I wan to be able to drag in drop cells in the A column (Just moving them up and down different cells in the A column) with out it changing formulas in the B column.
I tried the $ trick so that for example B10 = B9+$A$10 but if I moved cell A9 to say A10, I get a reference error in cell B10 (why is it doing this when B10 is locked with $)
I then tried going to format and locking the B column and not the A column and then protected the document but I still get the same REF error.
I know I can copy and paste with out effecting it but this is tedious, the whole reason I did this was so that I can move around bills EASILY and play around with what get paid when. There has to be a way to do this, but I'm probably searching with the wrong keywords.
I have a worksheet that, unless a form for data entry is activated, should be read-only for columns 'A' thru 'M' and 'O' with column 'N' left editable.
The form is activated by a button with a macro assigned to enter the data in the above columns when the form is closed. This has been compiled and works nicely with the sheet unlocked, however, as i need certain columns to be read-only unless the form is activated, i'm stuck with leaving the whole sheet unlocked for editing with or without the form being activated.
Is there any way a VBA code could be used to unlock the worksheet columns that are read-only when the form is activated, and then lock them once the form data is entered to the worksheet and the form closed?
Is it possible by using Visual Basic coding to position an image on a worksheet so that no matter what the screensize is it will always appear in the center of the screen, a corner or a side?
I am combining the information from multiple files--one per state, for several states--into a single large table so it's more easily reviewed. Each file has multiple worksheets, named for categories. The category/worksheet names are standardized, but not all worksheets are found in every file.
There is some overlap of items from state to state, so my macro copies key fields from each worksheet and pastes them into the new table and adds a column on the left with the name of the category/worksheet each item came from. It then removes any duplicates to create a list of unique items. Across to the right, the table has two columns for each state, one to show if the item exists there, and the other the date it was added.
In the final step I am using lookup formulas to populate the states columns, using the category/worksheet name from the left-hand column to identify which worksheet to pull from. When a worksheet doesn't exist in a source file, however, this creates an invalid reference.
Is there any way to use VBA to identify which worksheets are in a file so I can use the results in an IF/THEN statement to bypass any lines that would create the invalid references?
Worksheet one is "Company ID" info, in it are the columns:"Company Name", "street add", "city", "state".
Worksheet two is "Company Contact Person" in it are the columns: a reference to worksheet one column one "Company Name", and column two is 'Contact Person name'.
I want to be able to sort worksheet one at will, By city, or state, or any of the various columns. When sorting worksheet one I want the relationships in worksheet to to remain intact.
I am working on this project that involves opening several Excel spreadsheets and copying the data into a new workbook. The problem is that the names of the various worksheets I want to copy change every day - it always reflects the date. I open 7 workbooks, go to the sheet named (for example)01.17.07, copy a static range and then paste it to a new workbook.
Is it possible to record a macro and use a cell reference in the destination workbook (the one I want to copy to) to tell it which worksheet to copy? So, for example, I would type 01.17.07 in cell A1 of the new workbook and the macro would look for the sheet named 01.17.07 when running? Then tomorrow, I could type 01.18.07 and it would know to look for a different sheet?
Is there a way to prevent formulae from losing their references when the references get replaced.
I have formula which refer to a worksheet that is dynamic, meaning that the worksheet holding the data (CONTROL_1) differs from one query to the next is imported from another source. The datasource contents and format are identical.
I have a question dealing with a variable column and cell reference I'm using. To make it simple here's what I have done. On the FRUIT tab I have the following table in location BU132 - BV161
Name Column Apples A Pears B Corn 'VEGGIE'!A Peas 'VEGGIE'!B
I'm using the following formula to get the location of a cell on both the FRUIT and VEGGIE tab that has the value.
{where CH61-CH100 contains fruits or vegetables and where CN61 is a table that has the column reference which goes from 1 to 40)
This formula works great as long as the item is on the FRUIT tab. I can just drag the formula down the entire column and get my values for all the FRUITS listed in CH61-CH100. But, it doesn't work for any items in CH which are on the VEGGIE Tab. What syntax do I need to use in the cell next to Corn and Peas above to get this to work right?
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
Everytime I save my workbook, I get a dialog box that says:
"A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a vlid path, workbook, range name, and cell reference."
First off, it say's "this" worksheet no matter what worksheet is active.
Usually the culprit is a screwed up name that has a #REF in it somewhere. Not this time. All of the formulas appear to be working fine.
Is there an audit feature that will find the errors for me? (Excel 2007)
I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.
I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:
Sub SelectAll() For i = 12 To 20 Set curcell = Worksheets("Form Generator").Cells(i, 3) If curcell = False Then Cells(i, 3).Value = True End If Next i End Sub
As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range
On Error GoTo mEnd Set rng = Sheets("Log").[F14:F10000] If Not Intersect(rng, Target) Is Nothing Then If Target = "" Then With Sheets("Log")
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
