Link Books Together?
Jan 19, 2010
I have 4 workbooks that I want to link together. The first three are exactly the same as each other in the setup, cell size ect, ect. The forth book or "book4" is the book that I want link to the other three books. I am planing on using a countif formula to add up certain symbols in designated cell ranges. Eg, Make a formula in Book 4, cell B4 to count all the black triangle symbols "" in the cell range B6 to B35 in Book1, Book2 and Book3 on each work sheet (There is 31 work sheets which represents a work sheet for each day of the month). If I can just get the formula for doing this I can aply it to everything else.
One last question, these 4 books that will be linked togther are used for each month. So every month I save them under a that months name, but if I have the books linked I will always have to keep updateding the formulas in book4 b/c it will still have the formulas for the previous month. Is there a better way to setup what I want to do or will I just have to keep changing the formulas each month as I update?
View 4 Replies
ADVERTISEMENT
Jun 8, 2008
I am creating a new Excel sheet in a new Book, and I want to reference another cell in another Excel Book. OK I know how to do this.
But I want the following cells to "Automatically" get the corresponding cell values from the other Excel Book Sheet.
For example if cell B1 from Book2-Sheet5 gets the value of cell A7 in Book1-Sheet2, and B2 gets that of A8.... I want the following ones to get the corresponding values (B3 gets that of A9 and B4 gets that of A10, ETC...)
View 9 Replies
View Related
Mar 2, 2014
There are two books.First one is called Masterbook. This mah many formule, however in column 6,7,8 there are no formule.
Second book is called updatedbook.
This is linked with master book.
Therefore if masterbook cells are changed, the updatedbook be updated.
Is it possible if I change any value in column 6,7,8 of updated book , will automatically update the column 6,7,8 of master book by vlookup / vba.
View 2 Replies
View Related
May 15, 2007
Is there a way to reference a cel in another file without having Excel ask you to "update your location" for each one?
View 9 Replies
View Related
Nov 14, 2006
I use a code to work on all the sheets across all the files. But now I want the code work only on sheet named Paid across all the files.
Sub PP()
Dim wb As Workbook
Dim ws As Worksheet
Dim varWBnames As Variant
Dim varItem As Variant
varWBnames = Array("Book4.xls", "Book5.xls", "Book6.xls")
For Each varItem In varWBnames
Set wb = Workbooks(varItem)
For Each ws In wb.Worksheets
ws.Activate
ws.Range("A1").Formula = "=A2+A3"
Next ws
Next varItem
End Sub
View 9 Replies
View Related
Feb 13, 2007
Firstly... using VBA i'd like to disable any save function to everyone other then me and a colleague on a spreadsheet (The one below).... Is it possible that just the two of us will have the ability to save and no other user?
Secondly... On opening one work book... in this case "Productivity Test" i'd like it to simultaneously open "Productivity Test Back End" both saved in P:MI TeamNew Prod with the back end remaining locked totally.
The reason behind this is these two spread sheet's are linked via sums, productivity test uses the back end as a kind of data source so i'd like "Productivity Test Back End" locked so they cannot see it/amend it..
Maybe i could use a bit of code that updates the link everytime something is used as opposed to having the back end open at all...? What do you think?
The last thing, i have alot of macros that run quickly on my PC, if my manager opens productivity test and uses the spread sheet it appears to run alot slower.
There are alot of tables in Productivity Test that are hidden and only appear when needed while everything else remains hidden. The example below is one buttons code..
ub Selectdsrtablenew()
'Select the correct table for users to view DSR detail'
Application.ScreenUpdating = False
Rows("23").Select
Selection.EntireRow.Hidden = False
Rows("26").Select
Selection.EntireRow.Hidden = False
Rows("36").Select
Selection.EntireRow.Hidden = True
Rows("47").Select
Is there any way of stream lining the code here to make it smoother as it appears quite a few times in the report for different buttons.. Maybe defining table names?
View 9 Replies
View Related
Apr 8, 2009
I have a workbook called summary with a sheet called "detail" I have 4 workbooks Called week 1, week 2, week 3 and week 4. All the week workbooks have a sheet called Summary. I need to import the summary sheets from each week workbook into the the detail sheet. All workbooks are in the same folder. I need to only copy columns A,D,F,G.
If someone can post code or point me towards a thread I can figure what changes need to be made. I am getting better but slowly!
View 9 Replies
View Related
Nov 16, 2009
I'm trying to copy rows from one workbook that is exported from access to multiple worksheets in multiple workbooks. I used a macro I found here from JBeaucaire that will copy the data for me but it over writes the information I have in rows 1-3 and also the rows below, 28-35.
Is there a way to copy the data without loosing the information in the first three rows and the rows below where the data goes?
View 6 Replies
View Related
Apr 30, 2012
I have created a userform template and is working fine, the problem is the users are not able to minimize the template nor open any other excel files.
How to allow the template to open any other excel files or macro enabled files and also to minimize the windows.
View 1 Replies
View Related
Apr 8, 2008
I'm transferring data from one workbook to another. The first workbook ('Request Form') will always have a different file name depending on which user is saving it. However, the worksheet within that book will always have the same name ('Tab A'). Workbook and worksheet 2 ('PTS' and '2008' respectively) will remain the same.
Basically I want the macro to open 'Request Form' (whatever the name, wherever it's saved) and stay open as the rest of the macro runs. I need that file name to be a sort of wildcard, since the file path and file name will never be the same. How can I do this?
Dim TabA As Worksheets
Application.GetOpenFilename
Application.ScreenUpdating = False
Set wbOpen = Workbooks.Open("Request Form.xls") --THIS NEEDS TO BE A WILDCARD
'Declares the ClientName
Sheets("TabA").Select
View 9 Replies
View Related
Jun 14, 2006
I have string of tasks I need to automate so that they can repeat hundreds, maybe more than a thousand times. I have no experience with VBA, but I was looking through the VBA/Excel book published by this site and have been creating and adjusting enough macros for the last few days trying to figure this out. I have figured out the basic language and can follow simple instructions (big words scare me). Here are the tasks I am trying to automate:
1. I need to take a six digit string of numbers from one cell in what I have named The Primary work book.
2. Take those numbers and tack it on to the end of a file name (i.e. c:FileOtherStuffThisIsWhereOurNumberGoes).
3. Open the file, open the excel file within the file (which will also have a name using the same six digit number as before and maybe one other word/phrase, like rating or rating benefits).
4. Then in that workbook I need to go to the second page and take the result of one formula from a cell which will probably be the same cells (i.e. E8) in every file and paste that information in the appropriate columns in The Primary work book.
5. Repeat Step 4 in the same file, on the same page of the same work book, except one cell down (i.e. E9), which is also the result of a formula.
6. Back in the Primary Work book, we go back to the place where we started and go down one cell and the process repeats and repeats and repeats.
View 2 Replies
View Related
Jul 27, 2012
I have eight sheets that have lists of questions in, which I want to copy across to a results sheet if the answer to a question is 'Yes'. Each question takes up rows B:H inclusive, and I would want to copy them to rows B:H in the results sheet. The "Yes" value will be found in column F of each row.
How can I set up a macro to copy the entire rows (without formatting) into a results sheet properly? I've tried every solution I can find but always hit a roadblock somewhere.
Ideally I would like to have a 'populate' button on the results sheet that would find every question that was answered 'yes' across the eight survey sheets and import them into results sheet.
View 2 Replies
View Related
Jun 28, 2013
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
View 6 Replies
View Related
Feb 26, 2009
I have many workbooks which all have a summary sheet called summary, and which are all kept in the same folder (although this folder name/filepath may change from year to year).
I want to be able to set up the column headings and then copy the same set of summary information from each book so that it can then be used for analysis. The cells to be copies derive their value from formulae, it is the value that needs to be copied.
I attach a workbook with the format needed and the source cells. The source sheet will be password protected.
I know this is a question that has been raised before and I have searched the forum but not come across a question that is close enough to work from - my VB skills are appalling.
View 14 Replies
View Related
Mar 6, 2007
Ive put some sheet code together that i need copied to 12 sheets (jan to December) in 24 workbooks (each workbook has trhe same sheet names). I dont want to alter the actual content of the Excel sheets, I just need to copy VB code from a template (in VB editor) to the 12 sheets in each of the workbooks. Is this possible to do with VB or do i need some other utility since Im using the VB editor....
View 9 Replies
View Related
Aug 20, 2009
I have 6 spreadsheets all within the same folder, these are pretty much identical (rows, colums, sheets within them) apart from the names of the files.
I then have a master spreadsheet within the same folder where I want to combine all the data, from all the sheets within each book (if that makes sense!) apart from the data on the last sheet within each book as this is the reference data, onto one sheet within this master file. If possible I only want to copy rows accross which have complete data too.
So: (names not correct)
From book1.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet
From book2.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet
combine onto masterfile.xls on sheet1.
I have searched on here and can only find how to do it with the first sheet in each workbook, not looping through all the sheets in each book. Please see below.
View 9 Replies
View Related
May 6, 2009
I have created a DDE link which is connected to Excel and refreshes on every change but the problem is it's only 1 row in height, so as new data comes in I lose the previous. Instead I want the old data to move down a row and I want this to continue as new data comes in.
View 9 Replies
View Related
Mar 26, 2014
I have a couple of workbooks, workbook1 pulls information from closed workbook2. The current link works fine, it returns the value that is in cell E10 from the worksheet 'Totals' from within the workbook 'Week 12 Yellow.xlsm'.
[Code]....
What I would like to do is make two parts of this link variable; Name (so 'Marc owens' in the above example) Worksheet (so 'Week 12 Yellow.xlsm in the above example)
As stated, the worksheet in the formula will be closed so I cannot use the 'Indirect' option. I have come across a lot of talk about the 'Concatenate' option but this this isn't working, don't know if its the way I have the formula or if I need something extra adding.
Cell A1 = name variable & Cell A2 = worksheet variable
[Code] ......
But this just returns "G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals!%E%10" in the cell the formula is in.
I've changed it so that it is
[Code] ......
But again I get "='G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals'!%E%10" returned which is the same formula as originally used (top of page) but it doesn't return a value.
View 9 Replies
View Related
Nov 27, 2007
is it possible to create a link from Excel to MS Visio, to a specific Visio worksheet?
View 14 Replies
View Related
Jun 7, 2008
I have created a data base of all the spare parts we have at work.
In the last column I have entered a hyperlinks that take you to a layout of the store and shows you where the part is located i.e. 1C would take you to shelf 1 level C on the drawing in another tab in the same Excel workbook.
I would be pleased if anyone could help me by suggesting away that I can make the destination cell highlighted or more prominent once you have clicked on the link.
Also can I make the Find window automatically appear when the workbook is first opened?
View 10 Replies
View Related
Oct 22, 2008
If you are working in an excel spreadsheet that is linked to other excel files, when you double click on the cell that is linked to another workbook, it immediately opens the linked workbook if your settings are correct. How do you change your settings to enable this functionality?
View 2 Replies
View Related
Nov 17, 2008
I would like to have a link in a merged set of cells that puts the insertion point to another cell. Scenario: In cell k30, the user puts in the amount of credit card debt a individual has, and scripts the user based on that information. Later on, they get a more accurate running total of the amount of debt and need to change the amount in k30 for an accurate quote. After ther running total, in cell j172(a merged box for instructions) i instruct the user to be sure to enter the new total in cell K30.
Questions:
1. Is there a way to put a button or a link that would put the insertion point back up to K30 automatically, but allow the user to change it themselves and not change it automatically?
View 2 Replies
View Related
Apr 30, 2009
My workbook contains a link to a source it cannot find. I cannot find any reference to this source in my workbook.
I added a new blank worksheet
Deleted every other sheet
Deleted every module
Deleted every line of code
what remains is a workbook with 1 blank worksheet and a Link it cannot find the sourse file and I cannot delete this link. How can I eliminate this non-existent link?
View 2 Replies
View Related
Aug 27, 2009
I would like to ask if there's a way that i can create a link in a cell to another cell. Sample: in cell A1: there's a word "click to go to cell A150" in this case i dont have to use scroll down to go to cell A150.
View 2 Replies
View Related
Jun 20, 2011
Here it is
1. I want to put the AMOUNT on E6
2. from there on F6 put the account number on there (FOR)
3. Now once the account number is put on F6 it would link up the same account number on (H6-H32)
4. then when its linked up from those cells put the amount where you typed it up from to move to that account to THIS MONTH (J6-J32)
so from E6 to F6 it would link up, put an amount of 100.00 on E6 and an account number on F6.
Once account number is typed up it would link up to the account number to the other side to H6 -H32
Finally the amount that you typed up from E6 would link up to the account number and pop up to J6 - J32 (THIS MONTH) and if you put on other spaces on the cells it would add up different amounts and put account number and it would link up and the amount would move to THIS MONTH.
View 4 Replies
View Related
Dec 24, 2013
I attached one excel workbook...in that workbook main sheet & data sheet is there. I need formula in main sheet...while select drop down list in column of name & month data should come other columns. I tried vlookup ...but error came. I need drop down name & month only...other data should come automatically...
View 1 Replies
View Related
Apr 7, 2009
I wish to have a floating link in large spreadsheets that will always be visible and when clicked will return to a particular cell in that same spreadsheet.
View 2 Replies
View Related
Nov 2, 2009
I have a macro that works across 2 workbooks. In the middle of the macro, a message box pops up "This workbook contains links to other data sources" and I need to select yes or no. This is because I am using a vlookup. Is there a code that I can put in to the macro to select Yes to this box and therefore not stop the macro half way through??
View 5 Replies
View Related
Jul 31, 2009
I'm wondring how secure a dde link is? Can the source from where the data is coming from see what is within the spreadsheet that the ddel link is within?
View 2 Replies
View Related
Jan 29, 2010
I have a cell that a I want to get the results of a SUM to 20 cells in 20 worksheet out of 60 worksheets. When I enter my' =sum( shows up with serial_numbers. When I go to each sheet and select the cell I want (same in all worksheets) like in sheet 2 it shows my sheet name and cell. But when I go to my sheet 3 and select that cell it replaces sheet 2 with sheet 3 it does not keep adding the sheets with the cell numbers. When I select all sheet tabs it still only changes the sheet number in stead of running a range. I could type all the sheets names and cell but lots of work and I have a lot of this type of ranges to do.
View 2 Replies
View Related
