I need a formula to look up the value in another workbook. I have a number of workbooks that have a list o competitor's names and their scores The competitor names are not in the same order in each workbook. I have created a master workbook to extract the score from each workbook. I need the formula to match the name in another workbook and then pick up the score cells to the right.
View 4 RepliesI have a workbook with 5 or more tabs. One of the tabs is a CONSOLIDATION of all the tabs put together. I have columns on the consolidation tab with the names of the individual tabs. To the left of these columns is a list of general ledger numbers with their respective names. For example:
East West NE South
6103256 –sales
6540000 -salary
510000-travel
I want a excel to look at the individual tabs, for this specific gl number and name and, if applicable, return a value. What formula would do this?
My columns are not showing up correctly. East,West etc are the columns. 6103256 - are the rows
I'm running against this problem: a file should be run whenever people want.
however, the last part to complete the file is that the file from yesterday should be opened.
From this file data will be extracted via the VLookup formula...that's the plan.
However, with dim statements it won't work.
for now the code is:
Dim mynum, mysearch, this1, that1, other1
'Open the file from yesterday. Define the date string of this week and last week
mysearch = Left(mynum, 8) & Right(mynum, 2) - 1 'mynum is a date string like 2008-06-26
this1 = "Outstanding invoices " & mynum & ".xls"
that1 = "Outstanding invoices " & mysearch & ".xls"
Set other1 = Workbooks(that1). Sheets("Raw"). Range("comments")
ChDir "Z: FINANCEF&AAPInvoices overdue"
Workbooks.Open Filename:= _
"Z:FINANCEF&AAPInvoices overdue" & that1
the "this1, that1" etc appear when you point with your mouse in the code as correct but
the VLookup function won't work: it takes a long time and when interupted the macro the cells show the VLookup formula with "[that1]" instead of the workbook's name.
I also tried to dim that1 as workbook but that did not help.
New to using Excel formula's and am trying to create an IF/LOOKUP formula to look at multiple sheets within a workbook and display the information within the 'compare' sheet.The yellow cells are where data will be entered.
What I am trying to achieve:
Type the store numbers on the compare sheet (B2 and D2). For the sheet attached I have put 190 (in B2) and 2012 (in D2)
B2 store shows the predicted and actual values of 190 in columns B & C
D2 store shows the predicted and actual values of 2012 in columns E & F
(No need to worry about variance and difference columns)
So, if I change the store numbers in B2 and D2 to any of the sheet numbers, I want it to display the correct info for that particular store within the compare sheet.
I have attempted a formula, which you can see... I have basically looked at some previous sheets that had IF and LOOKUP on it and tried to replicate that for my sheet, but with no luck.
I have 3 Sheets named Paid, Rejected, and Reprocessed.
On the Paid and Rejected sheets I have 2 fields Customer # (Column A), and Amount (Column Q). (The customer # field has many duplicates but the amounts are never duplicates)
On the Reprocessed sheet I have all the rejected items (all fields) and also a field named Reprocessed. I need to use a formula that will check the Paid sheet for any items that have the same Customer # and Amount and return the amount
There are 8,216 rejected items and 45,047 paid items. Some items were originally rejected have been reprocessed and show under paid.
Any thoughts on which formula I should use?
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies View RelatedI have a workbook called Book1 that was created from another macro. I am attempting to do a lookup using columns B2 and C2 and look for the same values in columns G6 and J6 from a workbook called Marine Moves..If a match is found copy the value from Column H insert that value into Book1 Column A, if possible I would like the Marine Moves workbook to remain closed during this process.
View 9 Replies View Relatedi have 3 files :
1-april 2007
2-may 2007
3- total
i want a macro that that 1+2 and with "vlookup" out the cells in "total". i start to record the macro but ... it's not work at all. i want to put an input box that ask the user "what month and year" and then put the relevant file in the vlookup.
I want to lookup data in a closed workbook, that is located on my desktop, without having to open that workbook. I have been trying code as follows, but having problems with the syntax.
View 12 Replies View RelatedMacro (preferably) to look up some data in a workbook and paste it into another.
I have an active workbook with this format
Sheet1 Â ABCDEF1RECORDER IDDate HOUR IN KW Â Â Â
21000560805917/11/201100:15:00150Â 31000560805917/11/
201100:30:00150Â 41000560805917/11/201100:45:00150Â 51000560805917/11/
201101:00:00150Â 61000560805917/11/201101:15:00150Â 71000560805917/11/
201101:30:00150Â 81000560805917/11/201101:45:00150Â 91000560805917/11/201102:00:00150
I have another workbook opened called "Carbon intensity" with this format
CO2_Intensity_01082011_29032012 Â AB1Timeg CO201/08/2011 00:15466301/08/2011 00:30461401/08/2011
00:45460501/08/2011 01:00462601/08/2011 01:15463701/08/2011 01:30463801/08/2011 01:45466901/08/2011
02:004671001/08/2011 02:15472
I would like to insert the values in Column B in the "Carbon Intensity" workbook into Column F in the active workbook corresponding to the dates and times.
This is an example of what it would look like afterwards
Sheet1 Â ABCDEF1RECORDER IDDate HOUR IN KW Â Â Â
21000560805917/11/201100:15:0015046631000560805917/11/201100:30:0015046141000560805917/11/
201100:45:0015046051000560805917/11/201101:00:0015046261000560805917/11/201101:15:0015046371000560805917/11/
201101:30:0015046381000560805917/11/201101:45:0015046691000560805917/11/
201102:00:00150467101000560805917/11/201102:15:00150472
I'm using the following sub (thx Benvolio 2001) to do a vlookup (of A1 in Sheet2) and drop the resulting value ("found") in b1 of sheet2. Here's my question: I'm having trouble modifying the rng to use a closed workbook in another folder i:other folderotherfile.xls. The "Inventory" tab is in otherfile.xls
How can i do this? use the vlookup in a closed workbook somewhere else?
Sub Example_of_Vlookup()
Dim lookFor As Range
Dim rng As Range
Dim found As Variant
Set lookFor = Sheets("Sheet2").Range("A1")
Set rng = Sheets("Inventory").range("A4:D500")
On Error Resume Next
found = Application.Vlookup(lookFor.Value, rng, 4, 0)
Range("b1").value = found
End Sub
Column D = Dates to be used in each of the matches.
Cell C2 = data-validation cell, for choosing the specific Named Range that contains the data to be returned from the closed wb.
VBA code automatically opens the closed data file and then closes it after returning the data to the main file.
find the error in the cell formula (or named range formula) that’s preventing this from working correctly? (see atttached files)
What I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:
VB:
Function SHEETOFFSET(offset, Ref)
[COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR]
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function
This returns a value to a row in my mater sheet and I reference this for the data validation list.
However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.
Look up a cell value from another workbook and count the match
View 9 Replies View RelatedI have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:
Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.
I have two excel workbooks, i.e target.xls and source.xls. Assume
target.xls looks like this
letter number sound
a 1 a
b 2 be
c 3 cee
d 4 dee
e 5 ee
f 6 eff
g 7 gee
source.xls looks like this
letter number
a I
c III
d IV
g VII
In the target file, I want to be able to change the numbers of the letters found in the source file, so that the new target file will look like this.
new target.xls
letter number sound
a I a
b 2 be
c III cee
d IV dee
e 5 ee
f 6 eff
g VII gee
I believe there must be a simple way to do this using VBA.
I am trying to find a formulae that will sit in a summary sheet and lookup all of the worksheets in a workbook ( approx 80) for a particular individual's name and calculate the total amount paid.
The spreadsheet lists cash amounts by person for each week of the year. the individuals change, as they come and go during the year but the format of the spreadsheet remains the same.
I have attached the summary sheet and a few of the additional sheets. My formulae have fallen apart as I have been adding each person in manually, but I hope it will still be clear.
I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.
this is a portion of the forumula I am working with:
=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4
I have a workbook that's setup like a form, with 30+ worksheets in it (lets say this file is called "source.xlsx"). I have information in specific cells I want pulled out in each worksheet and brought in to a new workbook called "Destination.xlsx" and listed in a row.
View 2 Replies View RelatedExpanding this code to make it do the following:
1. Start w/ "RULE-Table" sheet,
2. Take note of "TODAY" date,
3. LOOKUP date in "RULE-table"
4. Locate Coordinates found in Col D of RULE-Table,
5. LOOKUP the data that resides in those Coordinates on the "PRODUCTION" sheet & COPY
6. PASTE data into "REPORT" tab C2 and E2.
Here's an example to visualize:
Code will do everything it needs to do within an Undisclosed OPEN file with several sheets. The reason the Workbook file name is undisclosed/undefined is because the file name will vary based on clients, so I don't want the code to be limited to look for a specific filename.
*The sheet names within this OPEN client file will always have the same names.
The sheet called: "RULE-Table" holds coordinates based upon "TODAY's" computer date.
For example: If today is JAN 4, the desired coordinates to pull data from are "B5" and "D10" from the "PRODUCTION" sheet.
"RULE-Table" sheet
A......B.........C................D.......
Row1.DAY....WK RANGE....QTR RANGE
2......1/1.......1...............B2,D7
3......1/2.......1...............B3,D8
4......1/3.......1...............B4,D9
5......1/4.......1...............B5,D10
6......1/5.......1...............B6,D11
Since today is 1/4, its rules say go to B2 and D10 of the PRODUCTION tab and collect the data
"PRODUCTION" (data source) sheet (within the same open file)
A.......B......C....D....E....F...
Row1..Header..Hdr..Hdr..Hdr..Hdr.
2.......2.................................
3.......14...............................
4.......13...............................
5.......20...............................
6.......62..............................
7....................76.................
8...................184................
9...................375................
10.................410................
11.................525...............
Last, the collected data from those 2 coordinates should always get pasted onto the "REPORT" tab into C2 and E2 in this example.
"REPORT" (destination) sheet (within the same open file)
A........B.........C.....D....E.....
Row1..Header..Hdr..Hdr..Hdr..
2...................20........410......
I have a formula in the Name Define part of excel in workbook that refers to another workbook. =IF(COUNTIF([DaysWorking.xls]Sunday!$A:$A,'2009'!I1)>0,1,"")
The DaysWorking.xls workbook is a data workbook, users may change the data and after they are done, they may exit the program but not save it. The DaysWorking.xls workbook should always revert back to the original form. I've put a read only property on it, but the problem lies when the user saves the file under Save/As function. Excel then updates the link to whatever the user saves the file as. Is there a way to make sure that the formula never get's changed?
I am creating a formula - that will look up a value within a seperate workbook. I want the workbook reference to change based on a cell which contains the date.
So workbook name is "Report 120309.xls"
Cell: A1 contains the date "120309"
Current formula ='[Report 120309.xls]Sheet 1'!$F$50. Can I replace the data within the formula with the date in the cell.
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?
Why this below formula is giving me a #N/A result when entering 'Motorcycle', '4x4' or 'SLR' into cell H2?
=SUM((LOOKUP(F2,{0,"Standard"},{0,8.99}))+(LOOKUP(H2,{0,"SLR""motorcycle","4x4","Standard"},{0,12.99,9.99,10.99,8.99}))+((LOOKUP(K2,{"Acrylic","Aluminium"},{0,7}))*IF(AND(E2="Y",G2="Y"),2,IF(AND(E2="Y",G2="N"),1,IF(AND(E2="N",G2="Y"),1,0)))))
If there is an error?
does anyone know if there is an easy formula to have a cell (h18) copy another cell (J2) if the value in a cell (h17) is greater than the last value entered in the row (17), and if it is not greater than the last value, or if there is no other previous value, to copy cell (K2)
View 14 Replies View RelatedI am using the following formula to look up data from a seperate sheet and cannot get it to work. all three numbers are on the seperate sheet ... some show correct and others show not there when they are,
EXAMPLE
DATA
A B
99993728569820 you found it!
99993728569820 no
99993767688837 you found it!
FORMULA
{=IF(A3=Sheet1!A:A,"you found it!","no")}
Is it possible to have a macro on vlookup formula? Tried the below code but the lookup result is not what it should be. I know this could easily be done a keying in the lookup formula without using code. But it would be faster if it could be done by using a macro.
Dim c As Range
Sheets("Data").Select
For Each c In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
c.Offset(0, 1).Formula = Application.WorksheetFunction.VLookup(Cells(1, 1), Sheets("Master").Range("A1:B10"), 2)
Next c
I got a table of two columns (A1:B6). One column (A1:A6) got temperature values and the other column (B1:B6) got enthalpy values as a function of temperature values as shown in the attached file. All values in the table are empirical values and I am trying to use them to obtain the enthaly (B11:B21) for my own temperatures (A11:A21).
I can use VLookup formula to do so but it won't be accurate because the enthalpy values are very sensitive to temperature changes. I need to use table A1:B6 and interpolate for temperature values that are located in between the integer temperature valuse. e.g. if the temperature=1.5 (between 1 and 2 in the table), how can I obtain the enthalpy value=15 (between 10 and 20) using the table.
How to arrange a lookup formula.
Book2.xlsx
In this example, I am attempting to look up one value based on two criteria. The month must match, and the location must match. In doing so, the value "1" should be returned in the example.
I have rows with text and numbers. In order to ensure that the numbers are accurate, I have a "QC formula" that calculates a check using all of the numbers from 1 row. The challenge is that the "QC formula" needs to vary depending on a text value within the row.
How can I lookup up the text value and then return the correct active formula for that row? I have too many differet text values to do a nested If statement. see simplified example below.
Condition ABCFormula' Needed based on Condition
Red123A*B*C
Blue123A+B+C
Green123(A+B)*C