Dynamic Name Range Incorporating VLOOKUP
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
ADVERTISEMENT
Aug 10, 2009
I have a spreadsheet containing all products in sheet 1. I have a list of promotional codes in sheet 2. In sheet 1 I want to say 'If cell A2 is contained in column B of sheet 2, then say YES, if not then leave blank'.
I've tried the following but it doesn't work:
=IF(VLOOKUP(A6,Sheet2!B:B,1,FALSE),"Yes"," ")
I get #N/A when the item is not in sheet 2 and #VALUE! when it is.
View 9 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
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
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
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
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
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
Jun 29, 2013
I'm trying to use loop to activate 3 workbooks "OSB1", "OSB2", and "OSB3" and their respective sheets. "OSB1" has sheet "OSB1" in it, workbook "OSB2" has sheet "OSB2" in it etc.
The digit on the end of each OSB is the variable I am using in the loop.
What I am trying to come out with is the following 3 workbooks and sheets being activated:
OSB1.Sheets("OSB1").Activate
OSB2.Sheets("OSB2").Activate
OSB3.Sheets("OSB3").Activate
I've given up the code as I can't work out the inverted commas:
VB:
Dim x As Integer
For x = 1 To 3
OSB" & x & ".Sheets("OSB" & x & "").Activate
Next x
Is it possible to do this at all? The bit after "Sheets" is correct. It's part with the first "x" after the first "OSB" which is incorrect now...
( In my actual code I'm trying to do more than just activate the 3 sheets but need to work out the syntax for this to be able to edit the remainder).
View 9 Replies
View Related
Oct 2, 2007
I have the following sendmail macro. However I need the "To" section to be the result of a lookup in a worksheet. The specific function that i need it to be is: =VLOOKUP(A837,'Tenacity Jobs'!1:65536,5,FALSE)
How can I incorporate this function so it will insert the result of the lookup into the "To" box ?
View 11 Replies
View Related
Jan 13, 2012
The following code (blue font) finds specific worksheets in an active workbook, and copies them into a new workbook then names the new workbook the same name of the worksheet. It all works perfectly.
The second code below (red font) imports some vb into the new workbook. It works fine if I manually open the new workbook and run it in the immediate window. However, I would like to incorporate the red code into the blue code so it all runs as one. I've tried inserting the red line after the "ws.copy after" line and after the ".saveas" line, but no dice.
For Each ws In wb.Worksheets
If UCase(Left(ws.Name, 2)) = "CC" Then
Set NewBook = Workbooks.Add
[Code]...
View 9 Replies
View Related
Dec 3, 2009
I have the below formula to make a simple calculation:
=SUM(AC1880+AJ1880)
I would like to adapt the formula to ask IF AG1880 is empty or not. If AG1880 is empty then I would like the formula to NOT make the calculation, but instead return "--" (nothing).
If it contains information, then I want the calculation (AC1880+AJ1880) to be made. If AG1880 contains information it will be text in a text format.
View 9 Replies
View Related
Oct 31, 2013
I have a number of files organised in a number of folders on Dropbox. I create a macro to enable me to be able to navigate to a particular folder and then get a list of all the files in that folder to be placed within certain cells on the spreadsheet I'm working with in Excel.
View 2 Replies
View Related
Apr 10, 2009
I'm trying to write a macro that will analyze data from one spreadsheet and do a regression. The information I want to be output on the same sheet. I tried to use the record function, but I got an error. It said "Run-time error '1004': ATPVBAEN.XLA could not be found. The code read:
View 2 Replies
View Related
Nov 12, 2012
I am trying out VBA to write a macro in order to average every 25 cells in a column. My attempt so far is
Dim myrow1 As Integer
Dim myrow2 As Integer
myrow1 = 2
myrow2 = 25
Range("G2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R " & myrow1 & " C3:R " & myrow2 & " C3)"
myrow1 = myrow1 + 24
myrow2 = myrow2 + 24
So I am hoping the first ActiveCell.FormulaR1C1 gets read as =Average(C2:C25)
However I just get Run-time error '1004' Method 'FormulaR1C1' of object 'Range' failed.
View 3 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
Jun 22, 2013
I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.
I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.
View 9 Replies
View Related
Jan 29, 2014
I'm trying to pull information in one spreadsheet (SS1) from another spreadsheet (SS2). I've found that the following works, except when SS2 is closed. Is there a way to do the following formula so that it will work even if SS2 is closed?
=VLOOKUP(A19,A:H,MATCH("Column Header",OFFSET(1:1,MATCH("Row Header",A:A,0)-1,0),FALSE),FALSE)
Link A19 is the lookup value
"Column Header" is the column header we want to find
"Row Header" is the unique row header for the headers of the table
The Column Header could appear anywhere in Column A to H, and the Row Header can appear on any row in the spreadsheet!
View 3 Replies
View Related
Apr 3, 2008
I am trying to merge data from two worksheets onto a 3rd for a mail merge. The COLLECTIONS sheet contains the acct #, name and amount owed. The ADDRESSES sheet contains the acct #, name and all of the address information. The 3rd sheet is the MERGE sheet that I'm using as the reference point for my word document and the mail merge. Upon reflection the MERGE sheet is probably not necessary, but made sense to have it when I started out.
Never the less...
On the ADDRESSES sheet I am trying to name a dynamic range "AddressList" (I'm trying to go dynamic because the list of addresses will change from month to month). I am using the following formula in the refers to field when I name the range:
=Offset(Addresses!$A$5,0,0,CountA(Addresses!$A:$A),CountA(Addresses!$1:$1))
I think this is what is giving me my problem, because the named range does not show up in the list when I try to go to the named range.
Just in case that is not the problem, the #Ref is showing up when on the MERGE sheet I type a vlookup formula referencing the acct # on the ADDRESSES sheet. That formula looks like this:
=VLOOKUP(A4,AddressList,3,0)
which seems simple enough. Before trying to make the named range dynamic the formula worked fine, which is why I'm thinking the named range is what's giving me the problems.
View 9 Replies
View Related
Apr 3, 2008
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
View 9 Replies
View Related
Jul 15, 2014
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies
View Related
Oct 28, 2009
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
View 3 Replies
View Related
Sep 13, 2007
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
View 9 Replies
View Related
