Vlookup, Using A Cell That Contains The Table Reference
Dec 12, 2008
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
Simplified Example:
I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE)
I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE)
This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
View 2 Replies
ADVERTISEMENT
May 27, 2009
Sheet 1 contains:
Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)
Sheet 2 contains:
Item Data
ABC 2
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
View 3 Replies
View Related
Mar 11, 2009
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies
View Related
Nov 11, 2008
i am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).
I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:
View 3 Replies
View Related
Nov 14, 2008
I have a sheet which uses a vlookup to find the data on a large sheet. normally to get the cell reference of the data i would use cell("address",......
However this appears not to work with a vlookup.
View 10 Replies
View Related
Nov 15, 2013
I am currently having real problem using the formula below:
=INDEX(Data!C:C,MATCH(B7,Data!A:A))
What i am trying to do with this formula is return the cell address of an item that is in a different sheet.
The reference the for search is in Cell B7 which is a date, it then looks at a different sheet "Data" for this date which is in Column A. It then returns the Cell Reference of Column C of the same row.
Currently it is just returning the value of column C and not the cell reference.
View 3 Replies
View Related
Jan 9, 2009
I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?
View 9 Replies
View Related
Jan 11, 2014
I am trying to create a lookup formula where the cell value to match has a formula behind it.
For example: =INDEX(AR7:AR371,MATCH(G28,AE7:AE371,0))
The trouble I'm having is that the value in G28 is derived from a formula and the Index Match formula then gives a #N/A result. If I change the value in G28 (a date) manually, so directly enter a date, the Index Match formula works ok.
Is there any way of getting the Index Match formula to work, or Vlookup would also do although that at the moment that has the same problem with G28 having a formula behind it.
View 4 Replies
View Related
Jan 22, 2009
I am trying to use vlookup so that when each site name is selected the relevant comments for that site are displayed.
I have a formula that is working, but it displays "0" if there is nothing in the cell. How do i get it to display a blank cell if thre is nothing in the reference cell.
this is the formula that i am using:
=IF($C$4="", "", IF($C$4="No Match", "", (VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0))))
The site name appears in C4, and is selected from another sheet in the workbook.
View 5 Replies
View Related
Jan 7, 2010
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
View 2 Replies
View Related
Aug 10, 2012
How can I do the following....if for example I have the following vlookup;
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false
How can I change the column index i.e. the 3, to reference to a cell.
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false
The above example doesn't work but I'm sure something can be done using TEXT or VALUE
View 3 Replies
View Related
Dec 12, 2012
In workbook 1, I have summary sheet with columns
A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate
Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .
View 5 Replies
View Related
Jan 30, 2014
I have problem with a spreadsheet that I am trying to create.
I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.
Now for planning purposes, I would like to see how much of each item I have on order.
I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:
=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.
For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is
A108: ="A"&MATCH(D11,'purchase order'!A:A,0).
Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.
View 6 Replies
View Related
Oct 13, 2011
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
Can I do a vlookup and keep my cell references?
View 6 Replies
View Related
May 21, 2008
I am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1
The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.
View 9 Replies
View Related
Jan 30, 2014
I know how to turn a column reference in a structured data table into an absolute reference:
=Table1[A] becomes =Table1[[A]:[A]]
However, how to make a cell reference, like this one, absolute to that it still locks on this row, column A when I drag it across. I don't want to use copy & paste, as I have other cell references that i need to leave dynamic.
=Table1[[#This Row],[A]]
I've seen that @ can lock rows, but I don't seem to be able to lock an individual cell.
View 2 Replies
View Related
Nov 23, 2012
I have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
View 1 Replies
View Related
Jan 27, 2014
I have a data table that looks like this:
RATE
Monthly
289.68
1.00%
262.81
2.00%
276.04
[Code]....
all the RATE numbers are manually entered.
now, if i reference the 3.00% number under the RATE column to the INPUT cell that I entered when I setup the data table, this happens:
RATE
Monthly
289.68
1.00%
262.81
[Code]....
this is obviously not correct, it is the value for the entry above.
and...
if i make ALL of the RATE cells then relative to the 3.00% one... (in other words 2.00% is actually =A5-0.01 and 4% is actually =A5+0.01)
i get...
RATE
Monthly
289.68
1.00%
262.81
2.00%
250.00
[Code]...
this is weird b/c the number for the first one and last one never change, but all the ones in-between change.
incidentally, the formula which is being solved is simply a 30k loan pmt for 10yrs ( =PMT(RATE/12,120,30000)*-1 )
View 5 Replies
View Related
Jul 7, 2013
I have a data tabel in Sheet 1 & Sheet 2
In sheet 3 they are combined via reference in order for me to do calculation in the input - They are combined in sheet3 as the columns are not identical.
Because the data-tables will be updated via sharepoint, i have to link to cells beneath the data-tables as i expect these will be fill out when the tables update.
My problem is that when the tables update, i dont see the updates in my "combination" sheet. it appears the the data tables add a new line, which moves my cell reference 1 line down.
Ex: Had a reference in Sheet1'E3 - after the data table updates the reference is now Sheet1'E4.
View 2 Replies
View Related
Feb 29, 2012
I have 2 option buttons to select a data table
option1 = XData1
Option2 = XData2
When I click the option I want it placed the table name in cell:p4
In a vlookup, I want to reference that table name in cell p4 ex: VLOOKUP(C13,p4,$C$7,FALSE)
I have also named cell p4 to xData and tried: VLOOKUP(C13,xData,$C$7,FALSE)
View 3 Replies
View Related
Apr 3, 2014
I have a spreadsheet with 51 worksheets - a "Master" worksheet and 50 datasheets (labelled 1-50). Within the "Master" worksheet the first column has numerical values ranging from 1-50.
One of the columns in the "Master" worksheet is a VLOOKUP that is trying to pull data, of which the table array is dependent upon the value in the first column. For example:
If cell A2 has the number 1 the VLOOKUP would be:
=VLOOKUP(E2,'1'!$A:$E,3,0)
If cell A2 had the number 2 the VLOOKUP would be:
=VLOOKUP(E2,'2'!$A:$E,3,0)
If cell A2 had the number 3 the VLOOKUP would be:
=VLOOKUP(E2,'3'!$A:$E,3,0)
etc.
Tried nesting an INDIRECT function with the VLOOKUP but it didn't work.
View 2 Replies
View Related
Aug 22, 2014
I need to make a vlookup which returns values for many 1000 material numbers. The values is located several other files. My problem is that i dont want to update all vlookups every time the other files (with my values) are getting updated.
e.g.
i want to have en cell A1: FileNameVersion1 and when someone updates the file the only thing i have to do is to change the name in cell A1 to FileNameVersion2.
View 3 Replies
View Related
Mar 4, 2014
I would like to have vlookup formua. And I would like the sheet for the table array section be linked to a cell value. So in my workbook this is my vlookup formula:
=VLOOKUP(B2,sheet1!B2:C8,2,FALSE)
I would like to to be something like =VLOOKUP(B2,(=b3)!B2:C8,2,FALSE). This way I can change the value in B3 and the vlookup formula will look for values in a different sheet as opposed to the one I originally designated in the formula.
Here is my workbook: Book1.xlsx
View 4 Replies
View Related
Jan 17, 2014
What I want is that I have a table like below (but it's long for 52 weeks) and long down with Vlookups. I want the formula with which I can just do the copy-paste and it will work. W1, W2.... are the sheet names with exactly the same formats inside.
A
B
C
D
E
5
W1
W2
W3
W4
6
Sales
10
#N/D!
[Code] .......
The base formula (for W1) is:
=Vlookup($a6;'W1'!$A:$B;2;0)
What I want, is the formula which instead of "W1" will write the sheet name which is in a row 5 (basically - cell name which is equal the sheet name), so with just dragging and moving the formula I will got the data from different sheets.
I tried this: =Vlookup(A6;'indirect("c5";1)'!$A:$B;2;0)
But I got #N/D! as in the example, instead of the numbers (yes, I put numbers into W1 and W2 sheets .
View 4 Replies
View Related
Aug 9, 2012
I want to reference a table (not a pivot table) from another Excel file. I want it to update automatically if the original table is changed in any way.
View 3 Replies
View Related
Aug 18, 2009
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.
View 3 Replies
View Related
Jun 2, 2013
I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.
View 9 Replies
View Related
Jun 5, 2014
I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?
I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.
i.e.
A B C D
territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555
and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.
View 3 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Jun 24, 2014
Vlookup a table, to another table that has multiple values.
E.g.
Table
GPASP002
Look up table - need to return second coloum but not the first value, all the values?
GPASP002 KZASP100
GPASP002 KZASP500
GPASP002 KZASP600
GPASP002 KZASP501
GPASP002 KZASP502
GPASP002 KZASP601
View 2 Replies
View Related
