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 RepliesIm trying to make a vlookup so I can say find a record where column A is >4, B is =2, C is 1.
Example - The lookup would find these 2 ranges.
7
2
3
2
6
4
[code]....
I want to create a cascading combo box from some values that I have, ill explain how I approached it.
Cascading combo box is a combobox which values are obtained depending on the answer of a previous combo box.
So I set up a little table like so:
Categories Car Train Bike
Car Mercades First Honda
Train Ford Western Ducati
Bike Skoda Red Suzuki
and the combo boxes would be set aside from that.I collected the data in Named Ranges (A Range for Categories, A Range for Car, A Range for Train...)
The first data validation box contains = Categories the second data validation box contains =INDIRECT($C$2) (Where C2 was the range of the first validation box)
Once this was working, I decided to change the formula for the named ranges to allow me to add more values.
The formula was: =$F3$F8
I changed it to: =OFFSET($F$3,0,0,COUNTA($F3:$F96),1)
I expected this to work however, it just doesnt.... when clicking on the drop down arrow for the validation box, nothing happens. No box or values or dropdown.
Is there a way to add cascading lists (from data validation or form/activex controls) to my excel spreadsheet WITHOUT using named ranges? Maybe structured references?
I need to avoid the named ranges because it will cause my workbook to have duplicate named range titles which I cannot avoid.
As a general example my issue arises because I have something like this where the titles are the same but they map to slightly different data. These also have to be cascading because the titles align to another list which I do not show in the example. I also considered using pivot tables, but the issue there is that the data validation lists repeat in the same worksheet. So I would have 3 cascading lists in row1 dependent on each other, but the same 3 lists in row2 dependent on row2 but not the previous row.
[Code] .....
Changing Ranges, Vlookup(), If() And Sum()
This is the link to the thread where ByTheCringe2 showed me the power of SUMPRODUCT and sorted my problem out. I don't think you'll need it, but I'd rather reference it before Roy reprimands me! lol
I am building on the same topic, though.
Hi All!
I have an investment where the monthly interest is added back to the capital. The next month earns interest on the higher figure and this higher interest is again added onto the capital and the story starts all over at the next month. OK, that's easy to do (Again, I think!).
All works fine UNTIL the end of the Tax year is reached and Tax on the interest earned is payable from the investment as the capital carried forward is reduced by the Tax payable. In the attached file I show how I calculate the interest earned for each following Tax year (which always ends at month 2) using SUMPRODUCT. Sumproduct is used because I never know whether the initial investment is made in month 5, 11, or any other month and by using certain controls (thanks ByTheCringe2!) I can simply total the interest earned in each tax year - the months between month 3 (start) and 2 (end).
On a seperate sheet I calculate the Income Tax payable as follows:
Firstly, I calculate the standard Tax payable on the investor's income from other sources. I then add the taxable portion of the interest to the standard income from other sources and calculate the tax payable on that combined figure. I then deduct the two figures from each other and get the increase in Tax payable because of the interest earned.
This "increase in Tax payable"-figure is then returned to the spreadsheet on which the interest is added onto the capital. See Sheet2! cells O9:O15. The Tax payable for each Tax year is placed next to taxable values I calculate using SUMPRODUCT (again!) in column N9:N15.
VLOOKUP is then used to transfer this Tax payable figure into the calculations (Column K9:K68) as and when required (only when tax is payable) - and should be deducted from the value in (Same row) Column H and that answer should be transferred to next row Column E (Balance at beginning of month) -
BUT it causes CIRCULAR REFERENCE faults and I am absolutely frustrated at not being able to figure out how to sort it out!
Sheet2, Column E9:E69 (See Sheet2!E19 for Comment explanation) in the attached workbook.
I have two workbooks wkA and wkB. I have a named range in wkB called BName.. can someone tell me whether it is possible to set a range variable (say, raA) from wkA to this named range in wkB. I have tried something to the effect of set raA = wkB.range("BName") but this doesn't work.. I've also tried set raA = [wkB.name].range("BName") to no avail. I'm wondering whether named ranges can actually be read from other workbooks.
View 4 Replies View RelatedI 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.
I am looking for the formulae in E9.
I may change the value in E8 as 1.5 or 2.5 or 4 or 6 I may scrolldown C9 as it set out there. I need to know the farmulae in E9.
I've information in two sections of a worksheet, columns A and B (range named "temp1") and columns D and E (range named "temp2"). I should also say that the reason I have two sections is that each section will have about 150 rows, so instead of having a huge list of 300 rows, I've tried splitting them into two sections.
AB
1a
1b
3c
DE
4d
1e
6f
What I want to do it show all the results that match the value 1 using a vlookup formula. Also the values in column A and D may change, i.e if value 4 in D1 changed to 1, I would expect to see "d" included in the results.
This is the original formula I got somewhere else which sorts out my vlookup on multiple matches issue.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Sheet8.Range("C16:Y1000")) Is Nothing Then Exit Sub
Dim rw As Integer
Dim arw As Integer
rw = 16
[Code] .....
As you can see, my code is located in the sheet8 worksheet object. Now, I have a few questions about this. Because I am located in the sheet8 worksheet object does that mean my code can only work in sheet8, i.e., the following won't work because I am in a Sheet8 worksheet object?
Sheet10.Range("B12) = ..... ....... .....
This is not returning a value in Sheet10? My question is how do I make my code return a value in Sheet10?
I have a spreadsheet that records the date, specific sporting activities and cash award for participation in a round robin sporting event that was held over 52 weekends. I'm trying to lookup the cash award value of each event the person participated in by using a date range within the vlookup formula.
Each participant has their own spreadsheet which they are able to access via server, and each spreadsheet looks a bit like this:
-Column A contains the date of an event in which the person participated
-Column B contains a category number related to the type of event eg marathon = 12, hurdles = 15, relay = 18 etc. This info is manual input
-Column C contains the amount the person will be awarded by simply participating in the event. This is a lookup value and varies depending on the date the person participated, so I'm trying to incorporate date ranges and 'if' formulas into the vlookup
I'm from Australia so my dates might be backwards...
Spreadsheet:
A B C
1 02/01/13 15 (supposed to be $10)
The formula should read a bit like this:
if (date at A1 is within date range 1st Jan 2013 and 31st March 2013, lookup B1 in table 1 column 2), if (date at A1 is within 1st Apr 2013 and 30th Jun 2013, lookup B1 in table 2 column 2)...etc for each quarter
I don't know if the following is right
=IF(AND(A1>='01/01/2013'+0,A1='04/01/2013'+0,A1
I love using vlookup, but what do I do if the value I'm looking up is listed more than once in my array? The default is that it will use the first value found.
I have a spreadsheet with 1000's of commodities and for each commodity the volume is broken out by month. For example, here's the sheet where I'm pulling the data from:
ITEMNOPERIODESTCSTREQQTYACTQTY
RC0202010010.109949140005867316
RC0202010020.109939312004668300
RC0202010030.109958968000
RC0202010040.109941769000
RC0202010050.109919656000
RC0202010060.109941769000
RC0202010070.109949140000
RC0202010080.109951597000
RC0202010090.109951597000
RC0202010100.109951597000
RC0202010110.109973710000
RC0202010120.109956511000
So my vlookup for the RC020 for the december Actual Quantity will default to the 5867316. For for January (period 2), if I do the vlookup it will still take the 5867316 again since its first in line.
So my question is, how do I alter my formula in January so it will vlookup the 2nd RC020 and give me the "4668300"?
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
I have a workbook where one tab contains the data that I am given:
Project-A data1 data2 date1 date2 date3
Project-B data1 data2 date1 date2 date3
Project-C data1 data2 date1 date2 date3
Project-D data1 data2 date1 date2 date3
In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.
So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.
I'm trying to hide groups of columns on a dropdown change. I'm extremely new to VBA, so I'm having a bit of trouble understanding some things. Here's what I've got so far...
Code:
Private Sub modeList_Change()
Dim selectedMode As Integer
Dim selectedOpp As Integer
[Code]...
I recognize my main problem is the assigning the multiple ranges to be hidden to the leadColsArray.
I have the following macro which runs vlookups between two sheets in excel. Whenever i add columns to the range the vlookup column reference is not the correct cell. Is their any way I can adjust the macro so that the column number adjusts in the formula when a new column is added??
Sub template()
Range("C7").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))"
'category
Range("c9").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))"
'sub-category
Range("c10").Select...................
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
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.
Here are 2 examples of the code I am trying to update:
I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?
[Code].....
For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.
[Code] .....
I am working with two sheets, Sheet one has 1 column of information with 12 columns to the right as categories. I want to be able to put an 'X' in any one of the 12 column rows, (category) and have a function that will copy/pastes the information from the main column (column #1, row=any) to the second sheet. I have tried True-False function, argument function, Vlook etc., I still cannot get it to work.
View 10 Replies View RelatedI need to create hundreds of named ranges going down a single sheet.
The name of the first range is in cell a1 and is 13 columns wide and 7 columns high (a1:m7)
The next name is in a8 and the range is a8:m14 and so fourth
If it's easier on a separate sheet I can have a list of names I want in column A and then the cells they refer to in column B.
E.g.
A B
Range1 Sheet1!A1:M7
Range2 Sheet1!A8:M14
Range3 Sheet1!A15:M21
I recall a few years ago that I located a program that allowed me to create an Excel spreadsheet and share it with others, but that locked the spreadsheet after a given period of time. I need to create a spreadsheet, but I want to put a time frame on it which requires users to request an access code from me every so many days. I don't want users in my business to be able to take the spreadsheet and use it should they leave the company. I know there was a subscription program that allowed me to do this, but I can't recall the name of it. It also allowed Excel to hide all of the formulas, even though the calculations worked.
View 2 Replies View Relatedcombining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that met my needs. I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me.
Desired output:
Item
Quantity
[Code]....
Why do my named ranges expand each time I import data from Access? I then have to go my named ranges and reset them. The reason I am using named ranges is because of the indirect functions I am using.
View 9 Replies View RelatedIs it possible to form a single Range object from ranges on multiple sheets. So for example, would it be possible to set a Range object equal to cells A1:D146 from Sheet 1 and A1:B49 from Sheet 2 and if so, what would be the syntax? For the first I have:
Sheets("SelectData").Range("A1:D146").SpecialCells(xlCellTypeVisible)
but I wouldn't know how to proceed from there.
I've used VLOOKUP based on matching one column and returning one result. No problem. Now I need to return one result based on 3 columns matching.
Assume this range of columns is named: TravelDays
Origin...Location...ShipVia...TravelDays
33778...420...UPS...3
33778...425...UPS...1
33778...440...UPS...3
33778...443...UPS...3
33778...446...UPS...2
33778...447...UPS...3
33778...449...UPS...5
33778...475...UPS...3
What I'm trying to accomplish is if these columns match:
i2=Lookup!TravelDays,1
E2=Lookup!TravelDays,2
N2=Lookup!TravelDays,3
My result should be: Lookup!TravelDays,4
I have a list of names I need to look up against another list. My problem is that the second list has a mixed formatting of Firstname Lastname and Lastname Firstname. What I did so far is create a second column that reverses the first and last names in the column.
Table1:
A
B
1
Name
Name (reversed)
2
John Smith
Smith John
3
Jackson Laura
Laura Jackson
4
Maxwell Ted
Ted Maxwell
5
Chris Hansen
Hansen Chris
Table2:
A
B
1
Name
Currently employed?
2
John Smith
=IF(ISNA(VLOOKUP([A1; Table1!A:A;1;FALSE));"NO"; "YES")
3
Laura Jackson
4
Ted Maxwell
5
Chris Hansen
The VLOOKUP formula works great but I need it to look for a matching name across Table1:A:B not just Table1:A:A. How would I do this?
i am trying to get a VLOOKUP to work over a number of different columns. An example the problem:
Sheet1
A B
1 10
2 20
3 30
4 40
5 50
Sheet1 is the sheet i want to lookup the values and i want to place the results into column B
Sheet2
A B C
1 10 40 50
2 20
3 30
Sheet2 contains all the infromation that i want to look for to make the comparison.
I know that the expression used for the VLOOKUP is:
Code:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The issue i have is "col_index_num" as i want to look over multipule columns. I have been using this expression:
Code:
=VLOOKUP(A2,Sheet2!$A$1:$J$20000,1,FALSE)
How do i change the "col_index_num" to look over all 3 columns and not just the 1st, 2nd, or 3rd?
Basically, I have an array of data (let's say A2:D20). I then want to do a VLOOKUP across the columns A:D, and return the corresponding value in column G, if VLOOKUP can find a particular value from column F, say.
So, I need a VLOOKUP function written in G2, that finds the value E2 in EITHER A2,B2,C2 or D2 (and return what is in E2, say).
I first thought I could CONCATENATE the strings in A2, B2, C2 and D2 and use some sort of VLOOKUP that finds a string within another string.
Range("B25").Name = "EndMull"
Its fine but if i insert a new row or column then it mucks the whole thing up. Is there away of naming them but if any cells, row or columns are inserted the range will automatically adjust to suit1