Unspecified Range In Vlookup
Aug 18, 2009
Im looking for a way to make the range in the vlookup below a little more dynamic by using the same kind of idea as ive shown in the MATCH part of the statement.
VLOOKUP(A1,Sheet1!F:Z,match("TEXT",Sheet1!$1:$1,0),false)
Basically i can confirm that 'Z' is my end row, and this remains consistent, however my 'start column' (F) needs to be more flexible as it may not always be column F, either by specifying a column name like using the MATCH function, or using INDIRECT
I was trying to use something like this, but im certain this is not the correct way of doing things (and it doesnt work anyway );
VLOOKUP(A1,Sheet1!(columnletter(column(match("STARTCOL",Sheet1!$1:$1,0)))):Z,match("TEXT",Sheet1!$1:$1,0),false)
COLUMNLETTER is a function to turn the column number back to the letter
View 9 Replies
ADVERTISEMENT
Feb 9, 2009
I've got a task I would like to automate but am not 100% sure on how to best do this. I have used the macro builder, but it always specifies a range of cells for my sorts and subtotals.
Essentially this is my issue, I'm responsible for an inventory report that I have to complete 3 times a week and they insist on using excel, not access. I merge my data, and always have to sort the data a certain way, then I apply subtotals to several lines, and then filter by one column containing the word total so that I can apply my formatting.
My problem is, I use three workbooks, with about 6 worksheets each and have to do it for every worksheet, but when I create a macro it always specifies a range, but the number of rows can change day by day!
Below is a copy of my macro, if you have any tips on how I can make this universal so that I can run one macro and sort, subtotal, and filter at least all sheets in one workbook and that would be great! In the example below I had 12,678 rows of data, but as stated before this changes all the time, and is different for each sheet (one sheet is all units, then each unit has their individual sheet also).
Macro:
Sub SheetTotal()
'
' SheetTotal Macro
' Total Unit Sheet
'
'
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Worksheets("EAB UNIT TOTALS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("EAB UNIT TOTALS").Sort.SortFields.Add Key:=Range( _
"D2:D12678"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal.........
View 9 Replies
View Related
Mar 21, 2012
I'm new to using VBA and have been recording macros and then trying to edit them where I need to and using Google to get the answers that I need, however I have hit a snag when I create a pivot table using my current macro.
The data sheet that I am creating my Pivot Table from will always have a different number of rows from week to week, but I am unsure of how to code this into my macro. When I recorded this, I used the range A1:S10000, which gives me (Blank) as the last row in my pivot table. change my code so that it only selects the data rows to stop these blanks appearing?
Code:
'Pivot Table DATA Tab to show Call Out Times for all Centres in file
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"DATA!R1C1:R10000C19").CreatePivotTable TableDestination:= _
"PIVOT!R3C1", TableName:="PivotTable1", DefaultVersion:= _
[Code]....
View 5 Replies
View Related
Oct 24, 2006
On a userform, I have a listbox named "eList" containing Employee Names.
I have 2 other listboxes named "List_eTrained" and "List_eNotTrained" which list the Training completed and Training not yet completed for the selected Employee.
When I click Name items, it updates both listboxes with information (using "AddItem").
Before it updates, I use this code to clear the info listbox items:
TrainingDetails.List_eTrained.Clear
TrainingDetails.List_eNotTrained.Clear
When I run the form and click on various entries, it works as expected. At random though, I get this:
Run-time error '-2147467259 (80004005)':
Unspecified error.
And only this line is highlighted in Debug mode: TrainingDetails.List_eNotTrained.Clear
Sometimes it happens after going through several names, sometimes it happens when clicking the 2nd or 3rd name. It's only happening on the "List_eNotTrained" line though--the other line is fine--which has me really puzzled.
I'm hoping someone else has run into this before and might know what's causing this random, unspecified error.....
View 6 Replies
View Related
Dec 3, 2013
I have a spreadsheet with columns A B & C
So in Column A would be the beginning range (E.g. 2000000) and B would be the end (3000000) with a description in C.
I have another spreadsheet which contain results between Colum A & B, e.g 212000 and would like to do a lookup to return the description in C?
Start End Description
10000002000000Result 1
20000013000000Result 2
30000014000000Result 3
View 9 Replies
View Related
Aug 1, 2013
suming a range, always staring with the "1" and then to what ever the vlookup is. so if the lookup value is 15 in column A, it would sum the %s from B1 to B15.
1
4%
2
2%
3
3%
4
3%
[code]....
View 6 Replies
View Related
Jun 11, 2014
I am trying to use a vlookup for two cells whose data are in a range of cells. The grid is in a range for weight and miles.
Weight
1000
1100
1200
[Code]....
For the example above, I have miles ranges in columns and weight ranges in rows. My output for the example should be 1025 as it falls in the 201-250 Mile and 1300-1399 Range. Is there a vlookup or a match/index that could find that output without looking through the grid?
View 4 Replies
View Related
Apr 10, 2014
It's a quick formula question that I have trouble solving..
See attached : LookupSample.xlsx
View 3 Replies
View Related
Jan 14, 2014
I am trying to do Vlookups across multiple sheets but cannot make it work. My result is #VALUE!
When I look at the Function Arguments it says the Table Array is Volatile.
My named range "Centers" has 19 tabs listed.
=VLOOKUP(A114,INDIRECT(""&Centers&"!A5:R395"),13,0)
View 14 Replies
View Related
Jun 12, 2014
I have a list (a) with distances and another list (b) that gives numbers to certain distance ranges. For instance:
[Code] ...........
Now, if I have a value of 4.5 in list (a), I want to look up the respective zone. For 4 classes, this can be done with an if formula (=if(4.5>50;Zone 4;if(4.5>25;Zone 3;if(..and so on))).
What can I do if I have lets say more than 100 ranges with respective zones (ranges not of equal size) because an if formula will not be feasible anymore?
View 3 Replies
View Related
Jan 10, 2007
Q1: In the range for the lookup I'm performing, the column with the possible match is the 2nd column. I've tested my function with this range, but it fails (range is $A$x:$K$x, with possible matches residing in column B). If I change the range so the 1st column has the desired data ($B$x:$K$x), it works. Can I modify the function to search using the 2nd column so I don't have to rearrange the columns in my worksheet?
Q2: Can I return an entire rold of data? If yes, how? I only know how to return one cell.
View 14 Replies
View Related
Feb 2, 2010
I am making a spreadsheet using excel 2003 to calculate sales and tax. Column B is formated for date (1/1/2010). Column D,E, and F are formatted for currency with column D=Total Price, column E=sale price, and column F=tax. In column B I type the date the transaction took place and in column D I type total price. Columns E and F are automaticly filled in with the formulas for same. In column H,I J row 9 I would like to calculate 1st Quarter Total Price, Sale Price, and Tax. Row 10 would hold 2nd Quarter info and so on. I searched for info but can't come up with any thing that works.
View 3 Replies
View Related
Jun 16, 2014
I have a 2 X 44 range of text cells in one worksheet. The first column includes the same array of variables that are found on a second worksheet. In the second worksheet I want to run a looping vlookup to check if the column in the second worksheet is empty, if it is not I want it to lookup the element in that column in that row from the first worksheet and return the 2nd column item to the adjacent column in the second worksheet.
When I run my program in its current form two problems happen. The first row in the range of the column I want the data returned in is empty, and for the rest of the rows the same value is returned, when it should in fact vary. I think part of the problem is that in my vlookup my entries are:
vlookup(CrudeType, Range("Sources"), 2, false) where crudetype is defined as Range("c2:c" & lrow). This is the C column on the second sheet, I want my output in the D column. Range("sources") is on the first sheet and covers D2:E45.
[Code]....
View 4 Replies
View Related
Aug 20, 2014
I'm trying to find a way to perform Vlookups against a dynamic range of data, where the number of rows/values in Col B is always subject to change.
The way that the workbook is structured is as follows:
Sheet1: Except for Cols A & B, this is a blank sheet. This is the sheet in which the vlookup values will need to fall into based on the number found in its' Column B
Sheet2: The sheet that contains the data that will be passed into Sheet1 via vlookup, and all of its Columns contain data.
So, basically, Sheet1 is a shell that needs to be filled with data copied over from Sheet2 based on vlookups against Col B.
The vlookups will need to be shifted back 1 column.
And examples of how the Vlookups need to work is:The value in Sheet2 [Col E], needs to get passed into Sheet1 [Col D]Along those lines:
The value in Sheet2 [Col F], needs to get passed into Sheet1 [Col E]
The value in Sheet2 [Col G], needs to get passed into Sheet1 [Col F]so on and so forth
So basically, all of the values passed into Sheet1 from Sheet2 need to be shifted back by 1 column until we reach the last Col (Col M in this file).
Where
The value in Sheet2 [Col M], needs to get passed into Sheet1 [Col L]
The number of columns will remain fixed, but again the number rows will vary week to week in both of the two sheets, so I would need to have a way of creating a Vlookup through VBA that accounts for the dynamic range in Col B.
View 4 Replies
View Related
Oct 31, 2007
Chik wee (as Borat might say),
I'm wondering? is there any way that you can use a named range for the array in a vlookup that is done in VBA?
my named range is "Lists" and here's my
View 10 Replies
View Related
Feb 2, 2009
I am hoping to do, is change a table's information based on a date range that is specified by the user. I have attached an example workbook below. In the example, on the Data sheet, there is the raw data information, showing user name, the date in question, and finally a number that refers to employee productivity on said date.
What I am hoping to do is to incorporate a vlookup function in order to change the data recovered on the Chart sheet by having the user select a date range. So that if you wanted to view the statistics for just Dec 15, 2008, it would only poll the data from that date, and if you wanted to view the statistics for any dates between Jan 1, 2009 and say Jan 30, 2009, it would do the same.
View 2 Replies
View Related
Oct 14, 2011
The workbook I'm working on has several sheets: Totals, Monday, Tuesday, etc set into a SheetArray.
The problem is that I'm trying to perform a VLOOKUP on data in worksheet "Monday" against column A in worksheet "Totals". However, the size of column A will change as the codes iterates through the days. Therefore, I need the range of column A in the Totals worksheet to be dynamic.
Code:
'Find any new projects in the daily data that are not in the Totals.
DayProjCount = Range(Sheets(SheetArray(A)).Range("AA2"), Sheets(SheetArray(A)).Range("AA2").End(xlDown)).Cells.Count
ProjCell = 2
With Worksheets("Totals")
[Code].....
During this process, the code should only find 1 error in ValidProject. But, each are throwing errors. I think it's because the VLOOKUP isn't searching through the correct range, but I don't know how to check it.
View 2 Replies
View Related
Apr 10, 2012
I have a workbook with 100 worksheets. Each worksheet is setup the same way with dates down column A, and data in column B. In another sheet, I need to run a vlookup on the dates and data, but I need it to adjust for whatever spreadsheet name I give it. Is there any way to have the vlookup table array change as I change the worksheet it should reference?
View 2 Replies
View Related
Apr 12, 2012
I am attempting to categorize IP Addresses. My lookup table looks like this:
Begin End Category
1.50.50.0 1.50.50.255 3
5.185.40.0 5.185.43.0 2
5.22.22.65 5.22.22.128 5
I have a list of IP addresses that need to be categorized.
IP Address Category
5.185.41.15 2
1.128.128.0 Null
1.50.50.128 3
The category column needs to be populated. The ranges in my look up table are not end to end (there are gaps).
Function Lookup got close, but assumed there were no gaps.
View 4 Replies
View Related
Sep 4, 2012
Is there a way that I can do a vlookup in 1 file and specify more than 1 data range to lookup the data in?
In this case I have one file to put the vlookup in and a second file with more than 1 tab and I want to have it search each of those tabs and return the result. The format of these sepatate tabs are to each other and for that matter, identicle to the main file.
View 1 Replies
View Related
Oct 23, 2012
My first worksheet contains the following information:
Column A contains parent category names (e.g. colours)
Column B contains lookup values (non-unique)
Column C contains value to return
Each subsequent worksheet is for a single parent category (i.e. a sheet per unique category)
I'm unsure how to write some code that will return column C for each worksheet
Example of first worksheet:
A
B
C
1
Group
Item
Amount
[code]....
From this, my code creates 4 new sheets named Red, Orange, Blue and Green with column A containing the item numbers that relate to the worksheet name in the table in the first sheet (e.g. column A in sheet Red contains item numbers 1 to 6 inclusive):
A
B
1
Item
Amount
2
1
x
[code]....
In column B, I could like to return the values in column C from the table in column C that are only specific to that worksheet name.
I think I want to filter column A in the first sheet against the sheet name and then perform a VLOOKUP on the filtered data or would using a dynamic range be better?
View 3 Replies
View Related
Feb 13, 2013
I want to run a vlookup (against cell D1 for this example), from a selection of date cells in column A (cell A1 in this example), where the date can be plus or minus a set number of days from the value in the cell (2 days either way in this example).
When I try the following it does not work:
if((A1-2)>A1>(A1+2)=D1,vlookup(D1,D:E,2,0))
Just to clarify, if D1=05/11/2012, I want the vlookup to return if cell A1 is equal to Nov 3rd, 4th, 5th, 6th, or 7th.
PS: =if(or(a1-2=d1,a1-1=d1,a1=d1,a1+1=d1,a1+2=d1),vlookup etc etc) returns the correct result, but if the date range I search on changes I don't want to rewrite the formula in this format.
View 7 Replies
View Related
Jun 26, 2013
I have a vlookup formula that works when looking up one specific value (ie F2 in example below)
=VLOOKUP($F$2,'All active PRs (RP6)'!B2:L2,12,FALSE)
But now, I want to lookup a range of possible text values (say F2 to F8 which would all have different text values). I've created F2 to F8 as a named range called ISS_Services and created a new formula
=VLOOKUP(ISS_Services,'All active PRs (RP6)'!$A2:$L2,5,FALSE)
But it doesn't want to work consistently. Can vlookup work in this scenario?
View 1 Replies
View Related
Jun 20, 2014
I want to do a Vlookup where the area/range that its looking at is named in a cell
So if my named Range is F226_0713 and that value is in Cell C13 How do I right the Vlookup to look for the Value in B17 in the named ranged identified in C13 and column index is 3 (rows over)?
View 5 Replies
View Related
Nov 16, 2007
I am trying to do a vlookup that currently looks as follows:
Range("C2").Formula = "=VLOOKUP(A2,SAP!A$2:AA$42,2,FALSE)"
However, the selection of the table_array (SAP!A$2:AA$42) is a set value. Instead I would like to select all the data that is on sheet SAP. The idea is that the data set will not always be A2:AA42, so I need to make this part variable.
Normally I would use
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
to select the entire data set.
View 9 Replies
View Related
Mar 9, 2008
I have been creating my own custom Estimate/Invoice system for my Renovations business. I am really stuck on something. I have scoured my Excell Bible, asked numerous message boards and noone is able to help. I did find a reference to my issue here but it still left me confused.
Here is my issue.
I created multiple lists for my materials database. They are broken down by categorie…such as Drywall, Tile, Doors & Windows, Hardware, Etc. right now I have 14 lists which I continually add items to as they come up.
Each list are exactly the same, 3 columns Item, Unit, Cost.
I am try to do a Vlookup to access the cost of an item from these list. Please keep in mind that no item is EVER THE SAME.
I can make a simple vlookup work for example searching my drywall materials database VLOOKUP(A1,Drywall,3,False) with Drywall being the preset list range.
However I want my Vlookup to search EVERY MATERIALS LIST for the specified item. I have tried inserting an array into Vlookup that looks like this (List names are restricted to save space)
VLOOKUP(A1,{Drywall,Tile,Hardware,Paint},3,False)
But I can not get it to work, I can get one single list name to work but not an array of them. Is this possible?
Lastly If statements will not work here as I have upwards of 14 lists and If statements only allows nesting up 7 times for a limit.
View 9 Replies
View Related
Sep 3, 2008
Here is an example of what I am trying to do. I have a date in column A on Sheet 1. In Column B I want to return the period for which that date falls.
I have created a list of Start and End dates for each period in Sheet2. The start date is in Column A of Sheet2. The End Date is in Column B. And the resulting Period is in Column C.
Basically if the Date in Sheet1 Column A falls in between the start and end date for a specific period. I want that period returned in Column B of Sheet2.
View 9 Replies
View Related
May 23, 2009
I have the following
title = Application.WorksheetFunction.VLookup(Q, Range(QandA), 2, False)
QandA is a range that is defined in the active workbook. The code excutes without error but TITLE does not get a value because QandA is reported as empty (when I position my cursor over it).
I have also tried:
title = Application.WorksheetFunction.VLookup(Q, Range("QandA"), 2, False)
View 9 Replies
View Related
Sep 30, 2013
I have this code which looks information in a table. The problem is that the table is getting every time bigger.
Here the code:
VB:
With Worksheets("Sheet10").Cells(9, 3).Resize(, LastColumnf - 2)
.Formula = "=IFERROR(CONCATENATE(VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,3,FALSE),""."",
VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,2,FALSE)),"""")"
.Value = .Value
End With
How can I write $A$10:LastNewRow... Another problem rises here, LastNewRow might end up being the oldLastNewRow, i think. But maybe since it is getting bigger, there is no problem, I don't know.
Instead of having a static reference like $C$550 I would like to have a dynamic one.
View 3 Replies
View Related
Jan 25, 2014
I am currently working on a project and encountering this problem with VLOOKUP.
I need to show the result into negative since it is negative on the table i made.
In my this scenario, it gives me this error "#N/A" even if the value is in the table.
Here are the details:
3 columns
RANGE Minutes
0.02
0.03
[Code].....
my actual table is upto minutes of +960 and -960 and still planning to go up until +1500/-1500
I have named the table as "TABLE" and the value is on "F3" I am using the command =VLOOKUP(F3,Table,3,TRUE) but if the cell is negative it return positive value how do i change the formula so it return the value to negative if it shoud be negative?
I also attached the file as your reference.
I need this to get the value in minutes if it is within the value in the range.
View 9 Replies
View Related
