I need to link to a different workbook but using cell values.
For example, in the attached file in cell B4 it looks like this:
='C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls]Week 9'!$D$20
I need a way to change Week 9 in the above formula using a validation list.
I would also like to know how to specify the above workbook referenced using a cell as well, for instance, C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls] would be the contents of a cell or the ability to click on a cell and have it bring up an open dialog box to browse to the file and input it's path into that cell.
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?
I have a question about linking between Workbooks. I have 2 Workbooks and some cells have Formulas targeting each other, formulas like IF, VLOOKUP... Etc...
I have it that way because the users cannot access other user whole information, even though there is no need of real time sync there is a need to share some of the information between the users.
So.. some cells with the formula appear as #REF, I
I 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?
How can I use VBA to set up multiple ranges & or Columns in Workbook 1 (Sheet 1) Col A:C and have them linked to Sheet 1 Col D:F in another workbook 2 EG. I want to run a Macro that writes data in Workbook 1 and have it show up in the other workbook. regular Copy/Paste is not an option. The macro writes to varying columns weekly in Workbook 1. Can I also link irregular named ranges across workbooks.
I am trying to put together a small rota/hours manager. I would like each employee to have their own workbook file and update it everyday. Then I would like to have a master workbook which reads each workbook and returns the data inside the workbooks when opened or on the press of a button. I have looked at paste special and other linking ways, but couldnt quite get them to work. Is it possible for the master workbook to keep the data retrieved in date order and then perform a calculation for each row/employee to calculate that days wages. (rate of pay stored in Pay_Rate sheet.) Finally, if there is an easy solution would it matter if the employee spreasheets were password protected?
There are two teams in my department, and each is assigned to maintain their respective work book and I'm looking to link them in order to save some time.
Team A - Responsible for receiving Invoices (Bills) and entering them in an excel spreadsheet when received and update when bill is paid. Only one tab in this workbook.
Row A - Name of company billing us Row B - Invoice # Row C - Invoice Amount Row D - Once Bill is paid the check amount is entered here Row E - Balance Due (Row C - Row D = Row E)
Team B - Is Responsible for maintaining a list of all checks issued. All of the checks issued to pay the bills received by Team A are entered here plus other checks to pay a variety of different stuff. On this workbook a new tab is created every month. One tab per month. Since we need to follow accounting rules and record the check NOT on the month it was paid, but on the month the service was provided. for example I might be paying a bill in the month of November for services that were provided in September, so I would need to enter this check in the September Tab.
Row A - Name of company check is paid to Row B - Invoice # Row C - Amount Requested to be paid Row D - Reason for payment Row E - Date of check issued Row F - Amount paid Row G - Check #
Here is what I want to do.
I want to link both of these workbooks so that when Team B fills out the information of the check issued this will automatically update the Workbook of Team A so that the balance is zeroed out.
He is my challenge. Workbook of Team B has multiple tabs so I can't just do a simple Vlookup and also every month a new tab is created (very dynamic workbook).
TO add to this in Team B's worksheets have to be in alphabetical order, which means that rows are inserted everyday. for example if I paid yesterday to A and C, I enter company A in Row1 and Company C in row 2 but today I received invoice from Company B so in order for them to be alphabetically I would need to insert a row between Row1 and Row2. So if I had links to this workbook they wold not update when the new row is added.
I have several workbooks in a folder with sub folders. How can I get the values located in C6, E6 and E9 and put in a new workbook the "file name" in Ai and those 3 values in Bi, Ci and Di respectively? Itīd be desirable get the values without open the files.
Got a requet from a client that we can't do with out accounting software. Basically I have a folder of all the clients indivisual invoices for 2008. We want to put together a report that will include the employee name, invoice date, and amount. But the only way to get this right now that I see is to sift though the hundreds of workbooks we have for them, one by one. Is there something that can be set up to pull 3, 4, or 5 of the same fields say cell A1, B3, C3, and D6 our of every workbook in a single folder on our network?
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?
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
I have been trying for months to get this to work by altering code from similar requests I've hunted down on google to no avail.
I am looking to have a "Master" sheet that is populated by data from 30+ individual excel workbooks. Each workbook is contained on a server in a directory "O:JobsJobs In Progress." In that directory are subfolders (named in sequence "C12000 Job1", "C12001 Job2", etc) which contain the actual workbook (Named according to number ie. "C12000 Jobinfo").
I would like this "Master" to automatically pull specific values from each C1200* workbook and paste them in a designated cell so that I can quickly look at specific information contained in each workbook. Each C1200* workbook is constructed the same way.
Short Example of C1200* workbook in which data will be pulled from:
Job Number C12000 PM Abe Lincoln Foreman Thomas Edison Contract Date 1/1/10
I am very new to using excel and I need connecting the information between two sheets. I have a weekly schedule that I want to use for my employees. I want that information to automatically be moved to a daily schedule. I need to be able to see how many openers closers and mid-shifts I have and how many hours I am consuming from my budget. I want the shifts from the schedule to be moved to the daily schedules in order in chronological order (morning shifts first, then mid-day/closers). If at all possible how I would like to have the span of their shift to be highlighted in the cells on the daily schedule.I will attach what I have so far to provide as much info as possible.
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?)?
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 work for a financial firm. Every morning I compare today's cash balance file (sent from our custodian) with yesterday's to see if there is anything different in each portfolio cash balance column. Right now I'm doing it manually checking row by row. It'll become tedious if more and more portfolios are added. I'm looking for an efficient way I can use the portfolio codes column (as a key) to compare with previous day's files portfolio codes column , and list only those portfolios(in another workbook) if the corresponding cash balance column doesn't match. This is what I started doing:
Private Sub compare_cash_bttn_Click()
Dim b1 As Workbook, b2 As Workbook, b3 As Workbook
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim i As Integer
i = 2
Set b1 = Workbooks("CashA")
Set b2 = Workbooks("CashB")
'Set b3 = ActiveWorkbook
Set w1 = b1.Sheets(qry4rCash).........................
I need to split this data based on column B = type of fruit e.g. Apple, Banana, Orange etc into different worksheets - e.g named banana with only banana related info. but still need to keep the same level of formating - colours etc.
I'm wondering if it is possible to read values from different workbooks and not open them first.
I have an overview sheet with values from the other workbooks, and I will not use relative references but us a macro that runs trough the workbooks and collect the values.
Im trying to come up with a Macro that will perform a =SUM() function across multiple workbooks. Some of these workbooks may exist, some may not.
For example, Summary.xls has a =sum() formula that picks up data from wbk1.xls wbk2.xls wbk3.xls etc, all the way to 100
Today i only have wbk 1, 2 and 3 but i know everyday more wbk's are added so i want to create a sum type function to accomodate the sbk's that may be created at a future time.
Right now when i try it, I not only get REF/# but on opening Summary.xls I get a popup that asks me to define the missing wbk's
I got a problem that I cannot seem to solve, I want to retrieve values from 2 closed workbooks, I have with the help of this forum managed to create some VBA that retrieves the values from one of the workbook. What do I need to think of,to be able to put in the values from the second spreadsheet without the values disapearing from the "master" spreadsheet?
The code so far:
Sub ValuesfromClosedWorkbook() Dim filetoopen As String Dim wb As Workbook
filetoopen = Application _ . GetOpenFilename("XL Files (*.xls), *.xls") On Error Resume Next Set wb = Workbooks.Open(filetoopen, True, True) With ThisWorkbook.Worksheets(1) . Cells.Value = wb.Worksheets(1).Cells.Value End With wb.Close False Set wb = Nothing End Sub
I'm just starting to experiment with passing values between workbooks and between modules and so far I've managed to get it working. However, I now can not run the receiving module independently because of the passed value ? Let me explain ...
I use start/end dates within my modules to create date sensitive reports. I have several files (for different departments) and each file has a module called "AbsenceChecker", I have been working on a way to create a Report Master workbook to run and amalgamate the "AbsenceChecker" module from each department.
Sub RunAllLoaders() Dim AbsenceStart As Date, AbsenceEnd As Date Dim PassVar1 As Date, PassVar2 As Date Dim DateStart As Date, DateEnd As Date On Error Resume Next 'start of data validation script continued in private sub. GetValidDates FromDate:=PassVar1, _ ToDate:=PassVar2, _ MinDate:=DateSerial(2007, 1, 1), _ MaxDate:=DateSerial(2007, 12, 31) Excel.Application.EnableEvents = False Workbooks.Open Filename:="R:RostersRosterALPHA.xls" Application.Run "RosterALPHA.xls!AbsenceChecker", PassVar1, PassVar2 End Sub...................
I have two workbooks with the same sheet names. One workbook is called "Main" and the other is called "Subset".
Column D in each sheet is mostly identical between the two workbooks and contains the LOOKUP references.
I would need a macro (called from the "Main" workbook) which allows the user to browse for the "Subset" workbook.
Once selected, the macro should cycle through all sheets of the "Subset" workbook and for column D values LOOK them UP in "Main" workbook and transfer the adjacent values from column C of the "Subset" workbook to column C of the "Main" workbook.
Appropriate error handlers need to be in place for:
- Selecting the appropriate workbook (i.e. one which contains identical sheet names) - The "Main" workbook is likely to contain additional sheets which should be ignored by the macro - Column D LOOKUP entries which are not found in the "Main" workbooki should be ignored (and vice-versa)
I have two workbooks, each with columns titled State Code & County Code. I would like to pull data from one workbook into the other workbook using conditionals. (Copy/Paste will not work as the file I am pulling into has MSA Codes in the same columns)
I often have a table (sometimes formatted as a table, sometimes just data arranged like a table but not formatted as a table in Excel) where I'd like to create individual worksheets (eventually workbooks) based on the information in one of the columns. Take for instance the attached file. I'd like to create a macro to create worksheets (or preferably workbooks) based on data in the Region column. So workbook 1 would be something like North Region Sales 2014, and contain only the data for the North region. Workbook 2 would be something like South Region Sales 2014, and contain only the data for the South region, and so on. Sometimes I might need to create these based off the Region field, another time I might need to do it based off of the Salesperson.
Region Sales 2014.xlsx
To do this currently, I'd use the filter and unselect whichever data I want to keep and then delete all the remaining (visible) rows. When I unfilter, I'm only left with the data I want. This works, but it takes a long time when working with 50 or more "Regions" and large amounts of data.
I want a macro to compare each value on workbook 1, Sheet "Destroyed", column "C" against each value on workbook 2, Sheet "ERT Master", column "B". For each instance where the value on workbook 1, matches a value on workbook 2, the row where the match is found on workbook 2 needs to be highlighted in yellow from columns A to H. This needs to be repeated for each value in column "C" of workbook 1.
I have a standard template worksheet called "Input" (the workbooks name is "Input Capture") which spans C12:U1100.
In addition I have a seperate worksheet which is based in an all entirely seperate workbook called "People Data" (the workbooks name is "Succession Planning") which again spans C12:U1100.
What I would ideally like is a macro which matches the value in column C and populates the entire row (much in the same way as a HLOOKUP, only pasting values) with the exception of columns M and Q.
Is there a macro that can create workbooks based on this data. So, i'd have one workbook that would contain all the Johns and all the other data in Columns A -Z, then another workbook for Ted, and so on...
The number of workbooks I need is not fixed. It all depends on how many names are in column C.
I want the code to run when we open excel workbook "TEST" and it should open up all the workbooks one by one in the folder J:ABC and copy cells C2 and C4 values in the A and B columns of TEST workbook.
e.g There are 5 workbooks in the folder J:ABC so when the TEST workbook is opened then the code should run and open 1st workbook and copy values in cells C2 and C4 to it and close the workbook.
The code should run as below:
1st workbook:
C2 value will go in Test workbook B1 C4 value will go in Test workbook A1
Close 1st workbook
2nd workbook:
C2 value will go in Test workbook B2 C4 value will go in Test workbook A2
close 2nd workbook.
3rd workbook:
C2 value will go in Test workbook B3 C4 value will go in Test workbook A3
close 3rd workbook.
and so on It will be going to next rows in A and B columns.
