If I have a spreadsheet that performs a VLOOKUP to an external sheet, is there anyway I can get it looking at a certain cell in my current spreadsheet to say which external spreadsheet to look at?
View 9 RepliesI have a list of file locations of .pdf files in column A of my spreadsheet. All the path and files have info on Sheet1.
i.e. Column A1 c: estpacking_1.pdf A2 c: estcountry_1.pdf
Can I use VBA to, when I run it, print all of the files in the list in column A1:D20?
I'm trying to summarize some data from an external workbook. The problem is that the worksheet names there are months (Jan2009, Feb2009 etc.) and the sheets rotate (change position) with time. In the summarizing workbook I would like to use a function to refer to (for example) cell A1 on the third worksheet, regardless of the current name of that worksheet. So I might have a cell in the summary workbook that looks something like "=CoolFunction(ExternalWB.xls, 3, A1)"
View 3 Replies View RelatedI have VBA code that creates three reports based on fields a user chooses. When the user clicks the button to create the first report, links are built to the files which contain the fields they chose. There are approximately 15 files that need to be linked in every report.
When the links are built, referencing the external files, the system is extremely slow.
I have Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual.
Despite these settings, the links pull in updated values.
Also, to create the last two files, I do a FIND/REPLACE to change the cells referenced in the links, causing it to choke.
would get the links in place without updating each one as it is created?
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
I have 2 excel sheets A and B
Sheet A has full list of (3000) of user names - First Name , Last Name and Email address
Sheet B has few UPDATED users (200) of user names from above list with UPDATED email addresses ( with First name , Last name and email address)
How should I update Sheet A with updates from Sheet B?
I have date on Sheet 1 and Sheet 2
what I want to do is take column A from Sheet 1 and compare to Sheet 2 column A if it's one Sheet 2 then on Sheet 1 column C and D post the results...
ie
Sheet 1
A B C D
ADDYY 64
Sheet 2
ADDYY4644454654
so then Sheet 1 would look like this
A B C D
ADDYY 64 ADDYY4644454654
Macro Button to copy and paste from different sheets, I need to Copy SKU which is in Sheet 1 Column B and Find in Sheet 2 then Copy the UPC on Sheet 2 and Paste to Corresponding SKU in Sheet 1 Under Column C. If an SKU doesnt show on Sheet 2, it should leave Sheet 1 Column C blank and go on with others.
View 3 Replies View RelatedI want to do a vlookup between two sheets, the matching criteria on one sheet is in the format xxxxxxxx ABC and each will only appear once. On the second sheet the same match may appear between 1 and 3 times, these will be sorted so will be beside each other. Is it possible to return the sum of the qtys for each time the criteria appears on sheet2 using a formula rather than the first one as a conventional vlookup would?
Sheet 1
Criteria Qty
xxxxxxxx ABC 100
Sheet 2
xxxxxxxx ABC 50
xxxxxxxx ABC 30
xxxxxxxx ABC 20
Result
Criteria Sheet1 Qty Sheet2 Qty
xxxxxxxx ABC 100 100
Is it possible to use the sheet name in a vlookup?
The current sheet name matches a value in column C of a worksheet called Circuits and I'm trying to display the corresponding value in column A on Circuits
I am currently trying to use a variable instead of an absolute name for sheets name in VLOOKUP, but it wont work:
Original formula:
=VLOOKUP($D6,ABG!$B$9:$O$12,3,FALSE)
What I would like:
=VLOOKUP($D6,$A$6!$B$9:$O$12,3,FALSE)
with A6 = ABG
I'm creating a blank woorkbook that I'm gonna use for different projects. The other day I've got a solution in this forum for a problem creating the link to a non existing sheet and it works perfectly BUT now I need even more. I have to use formula VLOOKUP in the same cell where I have my IFERROR formula and I can't figure out
More precisely, sheet 'Graphique 2' columns D, F, H... are refferring to sheet 'LOT' F25:F44 (sheet LOT is a model sheet and I'm gonna have more than 50 of it, for different companies). To create some graph in function of date I've added months for the next 3 years in column B sheet Graphique 2, so the money amounts from column F sheet LOT (reffering dates are in column C) have to be copied to coresponding rows in sheet Graphique 2
Normally I think I would have to write:
=VLOOKUP(B4,LOT!C25:F44,4,"")
except that I don't know the names of my future sheet so in the same cells where I should have the VLOOKUP formula I have:
=IFERROR(INDIRECT(C$2&"!f25"),0)
My question is what is the combination of this two formulas?
I've done some research and came up with this formula for Vlookup (see attached excel spreadsheet). What I'm trying to accomplish is to look up the values under 'Pipe Size' & 'Pipe Material' to give me a Heatloss number. The formula works with the Sched 40 Steel & Type K copper, but with the other 2 materials, I get #N/A. Can anyone tell me why thats happening?
View 3 Replies View RelatedI want my userform to search for a password entered in a textbox (in a userform) in a sheeton the same book, this is what i'm doing right now:
Private Sub CommandButton1_Click()
Dim RUT As String
Dim MATRIZ As Range
Formula is on Sheet1 and table array is on Sheet1
but the lookup value is on Sheet2 in Cell B15
Below does not work, either does anything I have tried.
=VLOOKUP(Sheet2!B15,B138:E161,4,FALSE)
Im have a vlookup which is specifying a table array from another tab on my workbook i.e. =VLOOKUP(J21,'Norwich CR'!$B:$AZ,6,FALSE)
I have another lookup which is feeding back the names of each worksheet (in this example its "Norwich CR". The range will be the same always $B:$AZ but i want the formula above to vary depending on whether my other look up feeds back "Norwich CR" or "Ipswich CR". how i can get =VLOOKUP(J21,'Ipswich CR'!$B:$AZ,6,FALSE) instead just by reading from another column of lookup data????
I need to fill in the third column on sheet 2 from the 3rd column in sheet 1. do I need an IF statement combined with vlookup??
sheet 1
mark l XYZ l 45
katy l ABC l 3
mark l ABC l 6
katy l XYZ l 45
mark l DEFl 4
sheet 2:
mark l XYZ l
katy l ABC l
mark l ABC l
katy l XYZ l
mark l DEF l
I have a Sheet named Main, Juz and another called wordforword.
Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.
I do prefer formulas over macro if possible.
I have a Workbook with 18 "sheets and a recap sheet. Each sheet will have a different number of rows between A9 and N60. On my recap sheet I want to be able to find what the last row with data in Column A and then be able to do a vlookup on that row to display the data in Column 2,3,4, exc. I know how to do the vlookup's fine but I don't know how to specify to select the last row. The data will always be different and the number of rows will never be the same either.
I tried the below formula because everyone complains that if you use True is returns the last row but that didn't work.
=VLOOKUP(A2,Sheet_2!$A$9:Sheet_2!$N$60,2,TRUE)
Is there a way to do this without VB, I don't understand VB at all and that would get way over my head really quickly.
Can you use VLOOKUP or a similar formula to lookup and add (or even better average) multiple data from one sheet?. For example the source sheet holds certain codes for each of our agents and I need the total of one particular code for each agent and then eventually work out the average for the day. But as the source sheet can hold 30+ agents it would be a long and tedious job to do seperate Vlookup's (especially as we are backdating the last year!).
I have attached a snapshot of the source data to try to explain what I need better.
From that source data I need to get the total/average of all the 101 codes.
I am using the Vlookup function to find a particular code in one sheet then give me the cost data that is associated with that code and put it into a similar cell in another sheet. It will work fine if I use (for example) A2 to E39 as my table_array. This would be fine if I only had 38 rows of data to look for the code on. However I have thousands of rows of data that need to be checked. Therefore when I change the table array to anything greater than E40, the vlookup will not work and it will only return a value of zero instead of the actual cost data. This is very weird and does not make sense that anything is wrong.
The only thing I can think of is that the vlookup function has a limit on the number of rows that it can look for data in. However, I did not think that it had a limit at all.
The task: create price sheet that calculates pricing based on 2 criteria - quantity, and production time.
There are two worksheets. #1 is the main calculator, #2 is the price sheet, broken down with time (in hours) on the left column B, the quantity across the top in row 1. There are then boxes with different prices based on both the hours and quantity of products.
On worksheet 1, I have specified quantity in C6, and time in C8. How do I pull a price in to F8 that calculates based off the time and quantity filled out on sheet 1?
I know vlookup can find specific text in a column when the formula resides on the same sheet. But for my actual application (a 15 page price list) I was attempting to use vlookup on sheet 2 and have it find specific text on sheet 1. Note that sheet 1 will be having additional rows added through out the table over time, so vlookup cannot use a specific cell location.
My attached example can simulate the problem by sorting the table by name (A-Z).
I am writing the code for a VLOOKUP in VBA..I was using the .Formula = "=VLOOKUP(LookupValue, LookupRange , Column No, 0 )"
But, the problem is that the LookupRange is to be done from different sheets everyday as the name of this sheet is going to be like 16th May,17th May etc.
The common thing is that this sheet is the adjacent sheet next to the one in which we are trying to get the VLOOKUP work...so what solution can i use.
I just started venturing into VBA. I'm trying to pull the data from a different workbook that will have the previous month as the sheet's name. I can't get the dynamic part of the code.
[Code] .....
I have a stock sheet of about 1500 items that is printed every 3 months for stock take.
The changes are then entered manually. I have set up a stock order sheet using VLookup and manually enter the items that need to be ordered. I'm wanting to know if i could use a macro to do this automatically and check only for the stock items that have changed (ie. items have been sold ). Stock levels needed on hand are usually constant but is changed from time to time so will have to be taken into consideration. I have included an example worksheet which would better explain what i have been doing so far. Also if possible could the macro generate a new worksheet for the order sheet and name it the corresponding date and month of generation.
on sheet 1 in cell C39 i need to find data from sheet 2 in row B that is 534 as in C32 is 11.2 and on sheet 2, 11.2 equals 534
View 2 Replies View RelatedI am trying to use a vlookup formula on a sheet with a table.
When I use =VLOOKUP([@[Loan Number]],Sheet2!A:B,2,FALSE)
It returns N/A as I would expect because I am not looking up based on the loan number.
When I use =VLOOKUP([@Processor],Sheet2!A1:B18,2,FALSE) the formula does work and displays the text "=VLOOKUP([@Processor],Sheet2!A1:B18,2,FALSE)"
I have also tried to reference the cell number with no success or =VLOOKUP([@[Processor]],Sheet2!A1:B18,2,FALSE) with no luck.
I want to use the Active Sheet name in a VLOOKUP formula....but no matter which way I type it, I get an error
Code:
Range("B1").formula = "=VLOOKUP(& ActiveSheet.name & ,CC Overhead Summary!B23:H36,7,FALSE)"
I'm at the can't see the forest for the trees stage.....
IF(ISNA(VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE))
It is the part regarding til indirect that gives me problems
This form I can get it to work
IF(ISNA(VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE))
But here I need the Omklist.xls to be open. And that I dont want.
The what i want is that the user can use the sheet with out oping the data sheet..