I have workbook 1 with information.
I have workbook 2 that contains cells that are linked to workbook1.
I have workbook 3 that contains cells that are linked to workbook2.
When I open workbook3 I would like it to be update without opening first workbooks 1 or 2.
Can it be done?
I have a a table linked between 2 workbooks.. when one updates, I want the other to update..
I have it working, however it will only work when both workbooks are open at the same time. I have it set to refresh on open however it won't work.
Is there away I can create a macro to control this..
I am redesigning a file with a list of too many links to simply list by hand, so I was hoping there was an Excel shortcut or some handy code that would help me generate a list of all workbooks linked to this one file.
View 9 Replies View RelatedI'm still using Excel 97. I know I'm a dinosaur but I find it does what I want, just like a Ford Model T would.
I have one workbook (workbook "A") which links to another workbook (workbook "B") when "A" opens.
If I now update and save "B" I find I need to close "A" and re-open it to get the updated data from "B".
Is there anything which might be called "update field"
I have a set up a workbook which is linked to numerous other workbooks to provide a summary of information in one location.
However, when we reach April the names of all the linked worksheets will change from 'name 09 - 10' to 'name 10 - 11'.
What is the best way of handling this transition, will I have to manually change all of the links?
I have attached a much simplified example, unfortunately I cannot post the original due to sensitive data.
Apologies if the explanation isnt clear enough, let me know and I'll give further detail.
I am trying to write some VBA that will select a row of cells that each have links to cells in another workbook, and then either autofill the formulas down (or pastespecial them down) for 20 rows. I have that part down using either pastespecial or autofill, but -
Since the forumulas are links to another workbook, Excel wants to resolve that link to calculate the values at the time of the pastespecial or autfill. If the sourced workbook is not open, the "browse for file" popup displays. I don't want the popup to display and would rather just get the #REF in the pasted cells and let it resolve the next time the sourced workbook is open. The end result would be like clicking "cancel" to the "browse for file popup", which I'm tired of constantly doing... I would just like to skip that popup all together and get the #REF value.
1. I am opening several Excel Workbooks and copy ( consolidate) this one into one worksheet in a separate workbook.
2. I want to close those workbooks automatically that I opened once it was copied and pasted to the Target worksheet.
3. The workbook that should remain opened should be the target workbook.
4. The codes below allowed me to open multiple workbooks at the same time and copy them to target workbook but I do not know how to close them automatically.
Sub OpenFilesOnNetworkDrive()
Dim i As Integer
Dim Filt
Dim FiltIndex
Dim Title
Title = "Select files: Hold Ctrl to select multiple files."
Filt = "Excel Files (*.xls),*.xls," & "Text Files (*.txt),*.txt," & "All Files (*.*),*.*"
FiltIndex = 1
I have basically read all the posts on similar subjects and have tried for a few days to make it work but my solution is far from good enough. So now I am asking for you help.
My project: I have a Master workbook that contains the complete sales for the entire business. This is based on input from three different departments. These three departments have their own Excel workbook that they enter information into. The four files are all located in the same directory on a network folder.
What I want to do is to automatically gather all the entries from the three slave workbooks to the master workbook whenever the macro I am trying to create is run. There is no way of knowing how many new entries each workbook will contain and they have to be added so that that they don't overwrite eachother and so that they are put in sequence after the last row in the master workbook.
Now the information to be gathered is located on the first worksheet in all the three workbooks. The destination sheet in the Master workbook is also the first worksheet in the workbook. The structure of the information that I want to copy is equal on all the worksheets meaning that one sale is entered as a new row in the sheet. It is this row that I want to copy to the mastersheet.
What makes it a bit more complicated is that I want to extract certain information from one sheet and different information from other sheets. From one sheet i want to copy the entire row and from another sheet I just want to copy certain figures like Order Number, Customer, Price and so on. Meaning that I will have a different set of what I want to copy depending on what workbook I am copying from.
Here is what I have so far, it is not working by far and I tried to aim for something simple to start with since my vba experience is limited. So far I cannot copy anything into my Destination master file...
I have a worksheet in my Excel wookbook that references particular cells from another sheet. I use a simple macro to alter the values in some of these cells, but for some utterly bizzare reason, the cells referencing them are not updated. The only way to update the cells is to click on the cell and press Enter!
I've never had this problem before.
Does anyone know why this is, or perhaps know of a line of VBA code that updates every cell on a particular worksheet.
Contents formula =countif(B1:D1,"YES").
This works fine.
Column1 has simple formula to count if text is equal to 'YES'.
But when I double click any cell in column 1 to open userform and update values in column 2,3,4 then the formula in column1 does not work.
Sample book attached : UFM.xlsm
I am making a report template in excel. I have written a macro to format the report based on the contents of two other sheets in the work book.
I am attempting to combine two cells in separate sheets as a linked cell in a third sheet.
i.e. in sheet 3, A1 I want to combine the values held in sheets(1).range("a1") with sheets(2).("A1") as a link which will update as the parent cells values are changed.
The cell has to be a link of the other cells as their contents will change based on the report requirements.
I have combobox filled with range of two columns , i want somehow to show them both in linked cell , I don't figure it out , by myself using BoundColumn option and it showing first or second column only.
View 4 Replies View RelatedI recently found out about .xlsb, which is faster and file size.. (mainly using it for the improved speed)
Now I've changed all the files at work to .xlsb and now link them all to .xlsm files that i can use to update the links to pull the data from the .xlsb files and then run macros as i need to.
However... I am having some issues with linked cells not updating automatically? is this because if the .xlsb format?
1. my understand was that .xlsb is the same as .xlsx just XML limitations.
2. my excel is set to automatic but i have to manually force it to update (and in some cases need to repoint the cell to the cell in the spreadsheet again).
I'm trying to create one spreadsheet where I use formulas to come up with an "end total" on a daily basis. I want these "end total" values to be automatically recorded each day on a separate spreadsheet that keeps a historical log.
In example:
On Day 1 I will enter numbers into "Spreadsheet A" to come up with an "end total" for that day. This number will automatically be entered into "Spreadsheet B" on the first row.
On Day 2 I will write over the numbers from the previous day in "Spreadsheet A" to come up with a new "end total". This new "end total" will be automatically entered into "Spreadsheet B" on the second row.
I'm working on right now is a spreadsheet where we catalog and monitor account information for a handful of groups. I have several different worksheets that contain grouped information such as all groups we have and a list of individual people in those groups. Those groups are in a single column going down and the people are in a single row for that group going from left to right, an example is below:
Master Personnel Listing for Accounts
-------------------------------------------------
Group 1 | Name 1 | Name 2 | Name 3
Group 2 | Name 1 | Name 2
Group 3 | Name 1
Group 4 | Name 1 | Name 2 | Name 3 | Name 4....................
I currently have numerous workbooks with linked cells to a master workbook, but the linked cells dont appear to carry over or retain the formating from the master workbook (e.g. font style, bold, underlined, font color, cell color). I make changes quite often to the master workbook and would like the changes carried over to the other workbooks. Does anyone have a script where it would automatically carry over the font formatting? I hope this is understandable.
View 4 Replies View RelatedNow he has another task that I am not having any success with. He has asked if we can make an Excel spreadsheet that allows us to access a list of customer units from a drop down menu which is in another workbook, and once you choose that particular unit, another drop down menu will allow only those customers who are assigned to that unit to be selected from that sub-menu. I hope that makes sense.
Say customer A1 (we will call him Delta Co, 2-1 Aviation) is selected from this first drop down menu (which I haven't figured out how to make yet either). Then this selection would access another worksheet in that other workbook with a new drop down menu with only the four customers in that unit. As an example we will use the names SSG Wilson, SGT Jackson, SFC Jones, and SGT Phillips.
So when the Supply Tech here opens the spreadsheet, the first drop down menu, he will select Delta Co, 2-1 Aviation. Then the next menu will only show four names, and he will select SFC Jones.
Once we get this going, I may be able to figure out the other things the commander wants, if not I will be back.
I have problems with dynamically adding and removing checkboxes. I have a form and there is a button to add more rows to the table. Every row includes a checkbox too.
So first i add a row, and then add a checkbox to a specified cell in that row.There is a button for removing rows as well (witch should removes checkboxes as well).
My problem is when i ad the checkbox the linked cell property only works for the first one.When i add the second row the linked cell of the firstly added checkbox changes to the one in the new row and the new checkbox has no linked cell. I am adding the stuffs as follows:
VB:
Private Sub addBtn_Click()
Dim y As Integer
y = findFunc("end") // Y define where To insert the New row
Cells(y, 11).EntireRow.Insert
Cells(8, 11).Copy
[Code] .....
Any way to add checkboxes dynamically.
I'm creating an excel sheet that is linked to several others. Basically my salesmen will enter their new prospects into their own sales sheets and that will transfer via a link to my main one. The problem I'm having is mainly with my sort.
When the information is transferred, I need the sheet to auto sort the Company names alphabetically and move any blank cells to the bottom.
I have a Database and every entry in the database has its own corresponding sheet with the data in a more viewer-friendly format. I want to assign a macro to a button to loop through all the sheets, copy the cells and paste to the database (to update the database).
Also, nothing prevents the user from deleting the sheets or mixing them. So is there a way to take the reference number from the database in column B, search for the corresponding sheet which has the reference number in cell B3 and then update it accordingly.
Code:
Sub Button19_Click()
Dim WS_Count As Integer
Dim I As Integer
[Code]...
Read about the Dfunctions and SUMIFS/COUNTIFS not working between linked objects and think my error is the same.
SYMPTOMS: Formulas wont work unless linked workbooks are open. Once open they work and as soon as the sheet is altered after they are closed, they links break.
My formula =OFFSET(('Linked Workbook'!$A$1),0,VLOOKUP(Range,RangeData,2,0)-1) or go to workbook and bring back a certain amount of cells to the right of A$1$ based on a lookup formula in the Main open workbook.
I'm using links to pull our sales prospects from our salesmen's individual sheets into one master sheet for reference. Once the prospect information arrives in the sheet, I need it to sort alphabetically by customer name so that we can see which customers have been visited and don't visit the same customer twice.
So far in VB, I've been working on this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5:L" & Cells(Rows.Count, "L").End(x1Down).Row).Sort Key1:=Range("B6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
But it's not sorting the way that I need to. All I really need is for the sheet to sort automatically by Company Name and for all of the 0's to go to the bottom. They are 'blank' data that will eventually be imported from the salesmen's individual sheets as they encounter new customers.
When selecting from a list of text items in a pull down menu in a cell how can you link that change to other cells to effect a change in them. An example would be if the pull down menu was in cell A1 and as a result of the text selected to be shown in A1 resulted in a need for the text in C3 to be changed as well as the formula in cell D3 to be change which will result in a numeric answer in cell D3. Not all of the choices in cell A1 will require that the text and formula be change in cell C3 and D3.
View 3 Replies View RelatedI want to establish a link from my worksheets. Using the conventional link method I was able to link the values from my first worksheet to the second worksheet. My problem is when I delete a particular row. The reference of the second worksheet will have an error #REF! since I deleted those cells. Is there any way that I can link my two worksheets without any error that even if I deleted a particular cell/row the reference is still intact?
View 2 Replies View RelatedI'm working on a workbook to track staffing patterns. I have two userforms included in my workbook. The first userform (userForm2) initiates upon opening the workbook. It's intended to allow the user to enter a date range and an office location for the report. The second userform (userform1) initiates when a command button (Weekly Summary) is clicked. I've linked texts boxes in userForm2 to cells in a hidden worksheet; this is where I'm holding the dates and office location until userform1 is initiated. I also have labels in userform1 linked to the same cells in the hidden worksheet so that when userform1 is initiated the office and date range appear at the top of userform1.
Here's the problem, when I click command button "Weekly Summary" the office location shows up perfectly however, the labels I have linked to cells in the hidden worksheet that contain dates do not update (i.e. they show the dates that were previously in those specific cells. I have to close userform1 and re-open it to get the dates to update.
I need the user to be able to choose a date range and office location when they enter the workbook. Then, I need userform1 to show the date range and office location (without having to open it, close it and re-open it) that the user chose on opening the workbook.
I am trying to create a macro that will create a number of check boxes, which are linked different cells. I have had some success in creating multiple check boxes and having them at the destination I want the problem is that instead of linking to different cells they are all linked to the same cell. I have attached a sample workbook SAMPLE.xlsx
View 1 Replies View RelatedI am designing a car park rota on a spredsheet.Each person has a limited number of spaces. Is it possible to automatically stop people going in and deleting out entries from previous weeks (to stop any cheating!) or would I have to manually go in at the end of each week and protect.
View 7 Replies View RelatedI have a cell with two Options - Applicable and Not Applicable. with the help of experts here in this forum I was able to hide the subsequent cells if the user chooses "Not Applicable".
However Now what I looking to do is - If the user chooses "Not Applicable" I want my subsequent cells to automatically change to NO.
Example : My C56 will have two Options - Applicable and Not Applicable
My C57 to C 70 will have a bunch of questions with two options - YES and NO.
If the user chooses "Not Applicable" in C56, I want the cells C57 to C70 automatically change to NO.
I have a large document control register and it is updated daily and posted to a cloud for many people to access. It is very time consuming to constantly jump in and out and highlight and unhighlight as many things as there are and keeping track of which items that need to be unhighlighted. The thing I'm trying to figure out is if there is a formula for this...If i change any cell in a row I want the row to highlight itself entirely, but after 24 hours I want the highlight to expire. Is there a formula or function I can use to make this happen?
View 4 Replies View RelatedI would like to fill some cells with a customer id if this customer is the same as the cell bellow. As my example below.
CUSTOMER idCUSTOMER NAME TYPE
737346 Nikos 81
1154765 Kyriakos 81
1154780 Thanos 81
I would like to fill automatically the empty cell bellow Kyriakos the customer id 1154765 in new column. I want in column "new" to fill with customer id and when the customer id is empty to fill the same customer id as above customer id.
Like the example below
CUSTOMER idCUSTOMER NAME TYPEnew
737346 Nikos 81 737346
1154765 Kyriakos 81 1154765
1154765
1154780 Thanos 81 1154780
