VBA - How To Make Range Dynamic In VLookup
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
ADVERTISEMENT
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
Apr 17, 2014
I have never used charts in excel unfortunately. I just finished watching a few tutorials but still a little confused. I have a WS with a list of items I want to make various charts of how do you make a chart that adjusts when new items are added? (thus dynamic data) Do you need a macro to do this or is it just standard by defining a range within that sheet?
View 7 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 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
Oct 10, 2005
I'm looking up a value from another workbook. Simple enough in itself, but
heres the thing.
I'd like to write a dynamic vlookup that checks the value of another cell to
find the table array for it to look up from.
Basically I have a table of data, the column headers are dates. I have a
report that is run daily that I need to look up from. Rather than implicitly
referencing each sheet I'd like to tell the vlookup to check the date in the
column header and use that for the sheet. The range inside of the sheets is
always the same.
View 15 Replies
View Related
Aug 27, 2009
I'm trying to create a dynamic Named Range using VLOOKUP in place of a sheet name. (Using Excel 2003 & Win XP Pro SP2).
To illustrate:-
Employees.xls contains employee's details on separate sheets for each department, e.g. Production, Admin, Sales, Personnel, etc. Each sheet is 12 cols. and 1 header row. Sheet 1 of this book contains a 2-column Master List of all employees and their departments. MyBook.xls has an employee's name in A1.
I can get his department by using =VLOOKUP(A1,MasterList,2,FALSE) and what I want to do is incorporate that into the following in place of 'Admin' so that the range will refer to the correct department for whoever's name is in A1.
=OFFSET('[Employees.xls]Admin'!$A$2,0,0,COUNTA('[Employees.xls]Admin'!$A:$A)-1,12).
View 9 Replies
View Related
Jul 9, 2008
I have an worksheet that I import a csv into, each day a new csv is added to the bottom of the previous csv data. I have some code that extracts the date and month # from the cell and places them in helper columns. The code find the last used cell in the helper column and the imported data column to find the first and last row of the new day.
This part works fine. However, I assigned a variable name to the first and last variables and would like to uses these row number to define a range in order to use the range for a vlookup or find operation. This is where I get stuck. I want to use column x and row (variable from first bit of code) to column y and row (variable from first bit of code). Then use a vlookup/find whatever works to find the text I need and get the data. Tips on looping the code would be welcome as well.
I have searched for answers to the problem, on the board and web, but have not found a solution that works for me. (at least that I could get to work!!)
Below is the code as I have it
Public Sub Enter_Date()
Dim DateA As Date
Dim DateB As Date
Dim Cnt As Integer
Dim End_of, Beg_of As Integer
Dim Count As Integer
Dim NumtoFill As Integer
View 9 Replies
View Related
Feb 10, 2010
I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?
View 5 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
Jan 4, 2009
=+IF(G6=0.25, VLOOKUP(F6,Maple,2), IF(G6=0.375,VLOOKUP(F6,Maple,3),"-"))
This function determines where on the "Maple" named range to get the data based on the thickness of the material (.25 or .375 from G6 dropdown). What I'm wondering is if VLOOKUP's 2nd operator (in this case "Maple") can be filled in dynamically based on another drop-down menu. I have 8 different species and I'd like to avoid a lengthy IF/THEN statement. OR, is there a completely different function that I should be looking at here?
View 3 Replies
View Related
May 16, 2013
I'm trying to make two identical worksheets in a workbook. I would like the second sheet reflect all the changes made in the first one. I thought I would simply use = but it doesn't work correctly if I add or delete rows. If I add a row between row 1 and 2 in the first sheet the formula in row 2 in the second worksheet changes from =sheet1!A2 to =sheet1!A3. The result is the same if I use absolute references which suprised me. I want the formula in row 2 to stay =sheet1!A2 whatever happens on the first sheet.
I don't care about formatting, just data are important.
The reason I want the same data on two worksheets is that the second sheet will contain some more data that should not be visible to everyone. I want to protect the second worksheet and require a password to unhide it.
View 2 Replies
View Related
Jan 1, 2010
why this isn't working?
=SUM(OFFSET(A4,0,3,1,(LOOKUP(9.99999999999999E+307,4:4))))
View 9 Replies
View Related
Sep 17, 2007
I need to compare two tables in two different worksheets (A and B) and update the weekly changes (income and sales) from B to A. I have been using an Index(Match) formula which has been working perfect. I am however forced to update to a macro.
The worksheets are identical and each item is identified by a unique ID.
Any ideas in exchange for a 6-pack Budweiser?
View 4 Replies
View Related
Oct 3, 2007
I would like to update a chart after I have updated the underlying data just by clicking a button and displaying the updated date in the chart. Does anyone know how to do this? Currently I am having to manually click on the graphs line in the chart, which highlights the columns data, then manually extend this for the chart to update. And then I am manually typing in a date cell in the chart. There MUST be a cool way to do this.
View 6 Replies
View Related
Aug 18, 2014
I have attached a workbook.
I have a calculated value in H2. This value will correspond to values in the table provided. This value is dynamic and will change based on criteria that will be added.
The value H2 is a combination of to "letters" D1 and A2 or A4 and B1 i.e. trace back where the intersection of H2 occurs within the table
In Cells A8 and A9 I am trying to represent which combination of letters will result in an intersection of the value H2. In the example provided it is C and A.
What i would like to do is have Cells A8 and A9 be more dynamic. So if for example the value in H2 changed to say R12,06 then the intersection would be B and B. Therefore Cell A8 = B and Cell A9 = B
The formula is cell A8 and A9 doesn't cater for this as the range is fixed. I need a dynamic range for the index and match function based on the where the intersection of H2 happens to be in the table.
View 4 Replies
View Related
Jan 19, 2010
It mentioned something about getting comments which display dynamic data as if something metioned in the cells will appear in the comment and on changing the data in the cells, the data in the comments also changes with it..
Unfortunately, I could not save that file or remember the website as my Internet went bonkers...
So can someone please help me with the same....
View 8 Replies
View Related
Sep 25, 2013
I am trying to send my field reps a spreadsheet that will allow them to pick the proper location for each building. The problem I am having is that there are ~45,000 buildings, each with anywhere from 1 - 92 locations. Here is how my spreadsheet is set up.
Sheet 1
Column A has the Buildings THIS IS NOT A DROP DOWN. THIS IS A STATIC CELL PRE FILLED OUT
Column B is where I want the drop down to be available for the rep to pick the location
Sheet 2
Column A has a list of the buildings
Column B has a list of the locations
I understand that normally I would need to create a named range for each building and its locations. However, there are more Buildings than columns in Excel. Is there a way I can do this using Index/Match, or Offset, or Indirect? I have a sample spreadsheet that can be found in my dropbox account using this link [URL]
View 6 Replies
View Related
Feb 8, 2014
I would like to repeat a formula and make its dynamic depending on the request.
The formular that I would like to repeat is:
=IF(ISERROR(INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3)),"",INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3))
View 2 Replies
View Related
Jun 8, 2014
I have this formula "=VLOOKUP(B5,$F$254:$G$258,2,FALSE)" I want it to return a "0" when cell B5 is not populated. How could I do that? Right now it returns "#N/A" and messes up my math down the sheet..
View 3 Replies
View Related
Aug 29, 2006
Is is possible to Cerate a ListBox in a Macro based on the following Vlookup
=IF($E$14="","",VLOOKUP($E$14,List!$M$2:$N$5,2,FALSE))
Have it pause for the users selection and return the correct value to E14 in the current worksheet?
View 5 Replies
View Related
Jan 7, 2013
It doesn't encompass the full spectrum of what I am hoping to have on this timesheet.
For some reason - when I select "REB (0730-1600)", it comes up to 9 hours and not 8.5 hours.
And I cannot have a =SUM column. My goal was to select a shift (Cell 5), it automatically input the hours worked (Cell 7), and then somewhere formulate a total for the week.
View 3 Replies
View Related
Nov 30, 2008
Hello there,
I am making a spreadsheet that tracks children's progress in school through the year.
I have a vlookup that turns their levels (2c,2b,2a,3c,3b,3a etc) into numbers so that the number of sublevels they have progressed during the year can be reported.
=IF(ISNA(VLOOKUP(E5,$AD$10:$AE$28,2,FALSE)),"0",VLOOKUP(E5,$AD$10:$AE$28,2,FALSE))
However, when children join through the year, they will not have data to go in the 'starting grade' box, so I was wondering if it would be possible to make vlookup see that cell E5 is empty, and so look in cell F5 instead. It might even, perhaps, have to look in cell G5 if they join the class after Christmas.
View 12 Replies
View Related
Feb 25, 2012
This is how my pivot table would look like to start. Over time I would refresh and more things will be added. I want to make a Pie chart, and everytime I refresh the Pivot I want the Pie to get updated.
[IMG][/IMG]
BUT I want to do this on a separate tab. So basically I want to do a Vlookup of sorts. Where all the Fruit items are pulled and automatically updated when I refresh the Pivot. Then I would do the same for Veggies.
View 4 Replies
View Related
Oct 28, 2008
I want to create dependent lists using data validation. The lists need to be created from ranges that will be growing as users add more data. I think the best way to create the list is a VLOOKUP. However, I am not sure how to use the VLOOKUP when the range is changing.
I have attached an example. I have a list on sheet "Vlookup" called "FRUIT" with "apple", "orange", "banana". Then to make the depedent lists I have created three other lists called: "APPLE", "ORANGE", "BANANA". I want to pull the COST from sheet "VALUES" into the lists "APPLE", "ORANGE", "BANANA". Users will be adding costs next to the FRUIT they purchase.
View 5 Replies
View Related
Feb 24, 2012
I am trying to create a vlookup to get a count of trouble tickets techs completed daily within a table I created, I am using a table since its dynamic. For example I need to see how many tickets Joe completed in a day. See below...
Tech Ticket# Comments Status
Joe 1234 Replaced HD Closed
John 3212 Replace Motherboard Closed
Joe 5678 Installed OS Closed
Above is just an example (my table has 40 columns). I need to vlookup joe and get the count of the closed tickets.
View 6 Replies
View Related
Jun 29, 2014
sample.xlsmI am trying to create a sheet with dynamic lookup based on selections.
So if a user selects planning or costing and then the state then click on search then it cell B13 it should lookup from data sheet and give full state name and in cell C13 give the document name and in D13 give the print rule.
If in the data sheet the document type says planning/costing and there is no corresponding state to that document then it should be incliuded in all searches.
When I click clear then it resets the search. So if i select Planning and state as AL then the display should be
Alabama ABC DEF
A12 AA1 (aligned to previous row)
C12 AA3 (aligned to previous row)
E12 AA5 (aligned to previous row)
Also is it a better option to do planning and costing as a radio button or drop down list. Is there a way in the state selections to show the drop down list arrow at all times. Currently when the cell is not selected it disappears.
View 10 Replies
View Related
Jul 14, 2014
I have the following issue I have a fixed value in column N this is vlooked up into a cell in the same row after a specific cell value (category):
Toilet
Category
X
Brush
Category
X
Handle
Category
X
Door
Category
X
Computer
Category
X
The table above shows that the initial column is fixed i.e Toilet, Brush etc however the X's are dynamic but they will always be placed after the word Category. I think a VBA solution is needed but I can't figure out how to do it.
View 2 Replies
View Related
Jun 22, 2007
how to get the table_array element of vlookup to either obtain details from a worksheet cell, or to enclose a variable (specifically part of the filepath) within the table_array formula. I have a template that gets saved and used in various directories, and I want Vlookup to to dynamically lookup information from a specific file that is also contained within the directory, without having to find and replace the directory name in the vlookup formulas.
View 2 Replies
View Related
