I have looked through the forum and haven't been able to find an answer. I have two workbooks. One has a worksheet with two columns - the first column holds ID's, the second column has the names associated with them. My second workbook has a large table with many different fields and ID's only. I would like to use Vlookup to add the names to the second workbook.
I want to link two workbooks together with the aim that when a option is selected in a drop down menu in workbook "band form", it auto populates the rest of the form with data from the second workbook "showsales2013". date, ticket price, support acts etc etc.......
as a example,the drop down in workbook "band form" is a list of bands (list data also taken from showsales2013) ive got this bit working ok (i think ), each band is playing on a different day so i have used this formula to get the "date" from "showsales2013"
this seems to work until i insert a new row and/or column (in the showsales wookbook - which needs doing from time to time as new bands get booked to play), then i get #REF in the date cell on the "band form blank".
i used pretty much the same formula to pull the other needed data (price/support acts etc ) from showsale2013, and the result is the same, #REF.
My master file(master database.xlsx) is kept open from which i have to Vlookup the values into these 700 workbooks.
The "agent Id" column is common in both the master database.xlsx as well as those other workbooks also.
The thing i want to do is i need the data from the "reporting supervisor" which is in the master database.xlsx file to be available in the G column of the other 700 books ( i have attached only 4 sample books). The G column can be then named as "Reporting Supervisor". Since "Agent ID" is available in both files we can vlookup taking this as a base. I have attached the 4 sample files out of 700 as well as the master database file in a zip file.
I have 5 worksheets in file Z on drive J which are full of VLOOKUP formulas pulling data from several files on drive K. Each worksheet pulls data from it's own file on drive K...ie, worksheet AB pulls all of it's data from file AB_2008, etc. On worksheet CD there are two rows which populate with #N/A when I open file Z, but if I open file CD_2008, the #N/A's automatically populate with the correct values. Any ideas??? I am not a programer but I have pretty fair excel skills...but this one has stumped me.
Is it possible to VLookUp Data between multiple workbooks (12 different workbooks to be exact) in one master sheet?
I would like the master sheet to find the empty data for the item number. Basically, how can I automatically populate the empty data for each item number in the master sheet. Every item will be different. The 12 workbooks are invoices throughout the year (Jan, Feb, Mar, Apr, etc) and these workbooks have the data needed to fill the master sheet. How is this possible? Or is it not?
Master sheet.xlsx -Contains item number -Purchase Date -Sell Date -Purchase Price -Sell Price -Profit Price
The 12 workbooks are the 12 months out of the year, which are invoices.
- Contain the data needed in the master sheet such as sell date, sold price, and profit.
The reason I would like to keep the 12 invoices as workbooks instead of worksheets in 1 big file, is due to the high capacity of item numbers each monthly invoice would have. For example (approximately 500 items in each workbook)
I have for hours and weeks, actually months, But I haven't found any sources on the internet, although I have seen the VLook Up how to's on youtube between 2 workbooks, but not the 12 I would like.
I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.
I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.
I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.
Right now, I have, on Source col L:
Code: =MID(C2,1,9) and in source M:
Code: =IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2) This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M
Then, in Log col R, I have an array formula:
Code: =VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)
This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)
Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.
I have 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets 2. Open Workbook A-2014 paste the sheets 3. Save and close Workbook A-2014 4. Close workbook A without saving
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows: =VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0) but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following: =VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE) The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
I’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.
I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?
I'm taking a spreadsheet that I produce each month and creating a year to date spreadsheet in the same format. I'm using a vlookup to find the campaign name in each sheet and add up the totals. This works fine but sometimes a camapign ends and so the vlookup for that month will produce an #n/a value so will reduce the whole sum to #n/a.
The VLOOKUP + VLOOKUP + VLOOKUP I was using that produced an #n/a is shown below.
=VLOOKUP($A6,'[Margin by Site Net April 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net May 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net June 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)
To get round it I've added in an IF statement combined with ISERROR as shown below. It works but is looking quite messy. Is there an easier way to do this ? (the formula below is from the cell below the one above so the look up value is one cell down)
I've got a workbook for every department which is used to make a weekly status. Each of these workbooks contains a sheet with the 'weekly statuses over time', i.e. a table showing the weekly status for the department for week 1, week 2, week 3 etc. I've attached the file to make it easier to understand what I'm talking about, see Weekly_Test. Currently I can only see the performance of each department but I would like to be able to see the total performance for all departments, i.e. have a 'Total workbook' where all the weekly statuses are summed up weekly.
In the Weekly_Test file you can see the code which is used to generate the 'weekly status' sheets (also inserted at the end of this post). All workbooks for the departments are the same, apart from the file name which is the name of the department and this does not change. The weekly status is generated in the following way:
In the sheet 'Indtastning' (data entry) you enter the data and the date. Then click 'Update'. The sheet is then copied to a new sheet with the date and all data is copied to the table in the 'his.status' sheet. Now, I would like to add some code so that when I click 'Update' the data which is copied to the table in the 'his.status' sheet is also copied to a similar table in the 'Total' workbook.
Dim indtastArk Dim arkDato Public Sub Opdater() arkDato = Cells(1, 1)
OpretDatoArk ' nulStilIndtastning overførTilStatus End Sub Private Sub OpretDatoArk() Sheets("Indtastning").Activate With ActiveSheet .Cells.Select Selection.Copy End With
It is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.
I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.
If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.
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 spreadsheet that I need to open two other workbooks to copy and paste data from.
The name of the two workbooks changes each day, and the location because they are filed in subfolders.
I have the following code, which works to open the first sheet but then comes up with the following error when trying to open the second sheet. "Run-time error '1004: Excel cannot access 'Hub PVA'. The document may be read only or encrypted".
The folder is not encrypted because when I was only opening the second document with the same code, it worked.
The code I am using is: 'DECLARATION On Error GoTo ERR1: ChDir "G:GENERALTRANSPORTDAILYPLANS" Workbooks.Open Filename:="G:GENERALTRANSPORTDAILYPLANS"
I still have a problem with consolidation of workbooks.
I am working in HQ and at the end of each month, every branch under my care will submit an excel workbook to me. Inside this are two worksheets (worksheet 1 and worksheet 2 for convenience). Both have standard headers and rows and rows of data. Upon receipt, I will keep all these files in a folder called "Oct06" (and Nov06 etc for future mths)
I'm not able to find an easy way to consolidate all the various worksheet 1 into one master worksheet. I need this so as to generate a pivot table. At the same time, I need to consolidate all the worksheet 2 also for the same purpose.
From a workbook, I need to open a further workbook called 'transfer' and then switch back to the original workbook. Presumably I need to save the current workbook name to a variable, open transfer with Workbooks.Open Filename:="c:debworkTRANSFER.xls" then switch back to the original
How do i pass the original workbook name to a variable, and then reactivate it. I have tried numerous options but just cannot get it to work. Do i reactivate with workbook. activate or windows.activate
I have this excellent code which I found on here that looks at the data in column A, looks at the data in column B & then returns the differences ie.
A_____________B_________C___________D______________E John________Jimi_________________Not in A_______Not in B Paul________Sporty________________Jimi____________John George______Ginger________________Eric____________Posh Ringo_______Scary_________________T-Bone__________Baby Posh________Eric Scary_______Ringo Sporty______Paul Ginger______George Baby________T-Bone
I have 5 worksheets, representing data from 5 days, M-F. On a weekly basis I open all 5 files and manually copy, and paste all into one large weekly file. Does not take very long so I am probably just being lazy but if I could find a simple solution to merge all 5 into one quickly it would be great.
The one solution I was planning on implementing was to cycle through all OPEN workbooks, copy the data into a new workbook, and close each workbook as the copy and paste is done. This will work but to me its 'dirty'. Each workbook only has one Worksheet.
In Workbook 1 O1, I need a formula that will, if Workbook 1 M1 = "Yes", find the value from Workbook 1 N1 anywhere in Workbook 2 Column E, and when it is found, look at the value 2 columns to the right in Workbook 2. If there is a value in this cell, return "Yes" in Workbook 1 O1. If there is no value in this cell, return "No" in Workbook 1 O1. If Workbook 1 M2 = "No", the formula should return "No".
I wrote a UDF that does some advanced lookup trick in a particular workbook. That workbook has to be open during this process of course. I used to have a copy of this UDF in several workbooks where I need to use it. This turns out to be bothersome since I needed to update the UDF frequently. So I decided to make an add-in.
I then copied the UDF to a new workbook, save it as an add-in (XLA), then go into Excel Options - Add-in (I use Excel 2007) and enable the add-in there. I then remove the UDF from each and every workbook that used to have it.
Now I close everything.
I then open the lookup workbook that needs to be open if I were to use the UDF. Here is where I don't understand. If I open a new workbook and use the UDF, it works fine. If I save the new workbooks then reopen them, the function still works. However if I open some old workbook where the UDF used to be and try to use the UDF, Excel gives me #Name? errors.
I have 85 workbooks in the same folder with a sheet in each workbook called "Budgets". Its the same template in each workbook with different data. I need to create a Summary master file of all my budgets so in the end I will have 1 Workbook with the 85 copies of the "Budgets" Worksheets. Does anyone know the VBA for this?