Using VLOOKUP To Retrieve Data From A 3D Array
Apr 25, 2006
I have created a workbook consisting of several blocks of similar data. To make updating the data easy I have arranged each block on a seperate sheet. IS there any way to retrieve data from this 3D array using LOOKUP functions based on data from drop down lists? I have tried labelling each block of data on it's own sheet and trying to retrieve the label from a list using VLOOKUP but each time the label is retrieved as a text string and gives an error message when used in another VLOOKUP. I.e. =VLOOKUP(A10,(VLOOKUP(A13,'0'!C21:D31,2,FALSE)),3+(IF(B10="No",3,0)))
The interior VLOOKUP is retrieving for example "a" instead of a, when the outer VLOOKUP encounters this it returns an error. Is there any way of fixing this, is it a shortcoming of Excel, is it me or will I ahve to go back to creating one big data array on a single sheet?
View 2 Replies
ADVERTISEMENT
Jan 17, 2008
I have coded a simple array. I have Checked its contents (I know it works as it returns values that i expect) with a message box successfully. All within a procedure in my only module of code. When i run a new sub routine, i can not code it successfully to extract the contents of the array previously created in my original sub routine. errors like
syntax
sub or function not defined.
View 3 Replies
View Related
Dec 20, 2013
I need to bring the new pac code NR. from sheet 1 to sheet 2 and when i bring them back only the 1st comes and when i copy past down it comes only the 1st number. I have changed all formats but simply doesn't work.
View 2 Replies
View Related
Mar 22, 2009
A newbie here.
I have numerical data in
range c3:c12
range f3:f12
Based on above two criteria I need to retrieve data in a Vlookup table, range gr1:gr510.
The Vlookup table range is gp1:gr510
View 9 Replies
View Related
Jan 8, 2010
I have to write some VLOOKUP formulas to pull several items from a report, the problem is there could be multiple items for each lookup value as the example below:
Company Data ABC Monday ABC Friday ABC Thursday DEF Friday GHI Monday
I need to reference every piece of data for every company but using the VLOOKUP formula, I am obviously only able to get the first instance of the lookup value (company ABC)
How to I write the formula to lookup the other data? The practical appliation I am using this for has anywhere from 300 to 400 lookup values.
View 9 Replies
View Related
Oct 29, 2012
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
View 4 Replies
View Related
Apr 26, 2009
I can't seem to get my head around the simple issue of assigning a range of cell data to an array! Here is a snippet of what I have, maybe you can see the flaw:
View 8 Replies
View Related
Sep 15, 2012
I'm trying to make this piece of code work to no avail yet... Every time I run it, the compiler pops up with Error #9: "Subscript out of range". The range consists of hundreds of cells in 1 column with geographic coordinates formatted as "General". Like this: myArr(38.91472, 37.20318, 38.63824, etc.). Tried to re-format the cells to "Number" - doesn't work either.
Code:
Sub searchArray()
Dim myArr() As Variant
Dim i As Integer
myArr = ThisWorkbook.Worksheets("Sheet1").Range("G1:G8594").Value
For i = LBound(myArr) To UBound(myArr)
Debug.Print myArr(i)
Next i
End Sub
View 2 Replies
View Related
Jan 25, 2008
I have a large table with data to which I want to retrieve a name to the left of a certain cell.
Clear as mud so far!
I've put together a small example of what I'm after. In cell F9 is the MAX of cells F2:F7. In cell H3 I then want a formula that will find the MAX of F2:F7 and return the text five rows to the left of that cell. In this example it would be Fred.
The second part of this question is how could I get it to display if more than one person had the same total....
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
Mar 3, 2014
I have created a userform with multipage, has two page that add new record in a excel sheet. Data has a unique reference no.(TxtRef.Value) for each record. I am trying to add a button to load the added data for a specific record using reference no back to userform so that it can be updated and overwrite back to the sheet in the same row, So far it is adding new record correctly. I do not know how to populate all the fields of the of an existing record and overwrite it back to the same row instead of adding a new record. Below is my codes
[Code].....
View 1 Replies
View Related
Sep 7, 2009
The formula I'm looking for should look for the name mentioned in B2 in range AC2:AF400 and retrieve the corresponding data below it starting from D11 to D41 and continue on F7 to F34 etc.
View 3 Replies
View Related
Feb 4, 2013
I need to match and retrieve data.
If cell A25 matches B3:B19, B25 should pull the Name and C25 the Cost.
I have attached a spreadsheet.
View 3 Replies
View Related
Nov 25, 2008
This code should retrieve data from sql server to excel sheet
filtering according to the "where" statement
its a modified recorded macro.
View 7 Replies
View Related
Sep 1, 2009
In the attached file for ODM AND ACCOUNT NAME ACCOUNT PAGE TWO PAGE C1 CELLS MEVCUT.BEN to the 2009/38 VALUE ODM PAGE AVAILABLE ON A TIME I ENTER: THE ENTIRE ROW A SUTUNUNDAKI of the SAME VALUE FILE ACCOUNT INFORMATION In the LISTELEMEK would HEATING
View 4 Replies
View Related
Jan 22, 2010
I still do not have my dummy files ready but wanted to start the discussion on Retrieving Specific Data form Txt file.
Later I will post sample files.
For now, I will appreciate any ideas on how can I refer to specific place in the txt file and get the relevant information to excel spreadsheet.
The criteria for searching the text file must be:
1.Look for specific ABC
2.right below must be a date: 01/20/2010
3.When those two criteria are met then go down and find the first occurrence of the word: “Test”
4.When you find the word, on the right of this word must find numbers
5.Get those numbers to excel
View 14 Replies
View Related
Nov 25, 2006
I have 4 columns of data:
Employee; Invoice #, Invoice Total ($), Month of Invoice
I am trying to create a simple report that will display Invoices, By Employee, for a given month. I have tried using the following VLOOKUP statements within an IF statement:
=IF(VLOOKUP(H2,S1DATA,4,FALSE)="OCT",VLOOKUP(H2,S1DATA,2,FALSE),0)
"S1DATA" is the table with the data I described above. I am trying to retrieve data for the month of October. When I use the above statement I get the information that I want, but when I copy the formula down (matching the employee name in H2, H3, H4... etc I continuously get the same invoice information.
View 9 Replies
View Related
Aug 25, 2006
I have a webpage with a form, where user can enter their login info and a location number.
- Based on these information I would like the location number to be fed into a Macro, when the user was authorized. The Macro then extract required information and give it back as txt-file ( this Macro is already written).
- The result txt-file need to be convert to an excel file and be availabe on web for downloading.
- -> All of this process must be automatically done. <--
My question is, which language (VBA, PHP, Perl,...) should I use to do the work and how can I do it?
View 3 Replies
View Related
Oct 11, 2006
I am updating one sheet within a closed workbook with information from one sheet in another closed workbook. I have been using a connection string similiar to this: "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:ExcelData2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""....
My question is, how do I make a worksheet that is in a closed workbook active. And to be more specific I need to change the name of the file I am saving to and I was hoping to do something like "activeworksheet.saveas=". If you happen to know another way that relates to the way I am connecting and updating information( SQL statements), could you let me know.
View 2 Replies
View Related
Mar 27, 2009
I have 2 sheets - one called 'Lookup' that has a table of recipes in the rows and ingredients in the columns. The cross reference of each is the required quantity of that ingredient, if any (obviously..). Both rows and columns are sorted alphabetically.
In the second sheet - Recipe - I have a subset of the recipes (e.g. 'Cakes') along with the applicable subset of ingredients. I've input these by hand, so I assume they can be referenced directly in the function. What I need to do is grab the quantity out of the table for each recipes ingredient. E.g. A6 will have the recipe 'Jamrolypoly' and H1 will have the ingredient 'Jam'. H6 needs to return the value from the table in Lookup.
I'm still (very) new to this but looking around I found the following which seemed to describe the function I am after:
=VLOOKUP(H1, Ingredients, MATCH(A6, Ingredients, 0))
(Ingredients is the name of the table in the Lookup sheet)
I then need to be able to use the returned values in separate equations on sheet 2 to work out costs. This bit I've done already but I've heard that some of the methods of data retrieval return 'N/A' if there is no value, and that would not allow any subsequent calculations using the value.
View 4 Replies
View Related
Oct 27, 2012
I have a list of zip codes in excel, which I need to pass to store website and retrieve all the store locations in that area..
For example:
[URL] .... "choose my store on the left"
or [URL] ....
Is it possible to do this in Excel/ VBA?
View 3 Replies
View Related
Jul 31, 2014
Is there a way I could achieve copying cell's content minus some characters?
For example in the cell A1 would be: "two apples"
I would put into the B1 cell something like =A1(-4charactersincludingspace) - the result in B1 should be "apples"
Is it understandable what I mean?
View 1 Replies
View Related
May 7, 2009
A while back i got some assistance from a very helpful member with a formula to retrieve a value from the left most column, that was greater than Col "min" and less than col "MAX" from a range of values.
I never really worked out how this functioned and can’t really make it out of the help files on excel. I am now trying to do a similar lookup from a table of values but only need to find the row value using a greater than less than operation, using a single column.
The original formula was
{=INDEX(A:A,MIN(IF((INDIRECT("MAX")>B3)*(INDIRECT(“min")<B3),ROW(INDIRECT("min")),"")))}
Where B3 was the number to be compared against.
View 2 Replies
View Related
Sep 30, 2008
basically all it does is retrieves external data with the preferences that I set... what I want to do is change the code so that instead taking the same URL address it refers back to a specific cell in a workbook for the address.
View 3 Replies
View Related
Apr 27, 2009
I need a macro that will ask for a crew # and then select all the rows off another worksheet that contains that crew #. Then take the selected rows and copy them to the active worksheet for reporting.
Possible?
View 12 Replies
View Related
Nov 6, 2009
I need a macro to do the following:
I need Invoice # from column A to put in Column I by comparing Name in G and H with Name in D and E.
For Example
Compare Alfred Whitworth (Column G & H) in Column D & F would give Inv # 88530 ...this value will go in column I.
Compare Angela Owens and could not find in Column D and E so leave Column I Blank.
Compare Annabelle Deen would have Inv # 88740
And so on...
View 9 Replies
View Related
Feb 1, 2010
I need to move data from "2010" page to page "sheet 1 (2)" so I don't have to double chart.
I want to be able to retrieve data in columns w, x, y, z, ac, and ad, on page 2010 and place them respectively in coloumns I - N on the correct row. (the row that matches the right name)
I dont' know how to do this-I don't know if it complicates things that 2010 has breaks in the data (each month has totals and are broken up. ) can anyone help?
I believe I have xcel 2003 if it matters.
View 4 Replies
View Related
Feb 10, 2013
Im wanting to get a function that can get the results I have in cols B and C from the data in col A .
I already have functions to get the 1400m and F1 as in cell A2 etc .
Sheet1 Â ABC1DataClassStakes2Mdn 3yo $6K 1400m F1Mdn 3yo$6K3OPN HCP C/WGT 1000 SOPN HCP C/WGTÂ 4HCP $58K 1110m GHCP$58K5GRAZIA STYLE $177K 1100m GGRAZIA STYLE$177K60 - 91 $80K 1200m G0 - 91$80K7Rating 70 SW+P $10K 1400m G3Rating 70 SW+P$10K8MDN 2YO C/WGT 450 DMDN 2YO C/WGTÂ 9OPN HCP $100K 1400m H10OPN HCP$100K10Mdn & JMP HWT $6K 2100m G2Mdn & JMP HWT$6K
View 2 Replies
View Related
Jul 20, 2013
I am using Microsoft outlook to get my all official emails. I have lot many emails in outlook express & there are some common things which i want to get in an excel sheet. Details of data are as under.
Column A Column B
1) Company Name Xyz Ltd.
2) Sales 1500$
3) Commission 150$
4) Total Sales 2500$
how can i get particular these data from outlook express to excel sheet ?
View 1 Replies
View Related
Sep 25, 2013
On sheet1 I have 6 products in E15:E20 and their prices in AB15:AB20.
I have created userform1:
Step 1. When the form shows I'd like the products in E15:E20 to show under "Product" and prices in AB15:AB20 to show under "Price".
Step 2. The user needs to be able to add a price value in either the "-Disc" and/or "+Fees" fields, from this the adjusted price should show in "New Price"
Step 3. When the user hits "Add", the value under "New Price" needs to show up in AB15:AB20. To complicated this last step (maybe) in cells AB15:AB20 is:
Code:
=IF($R15=0,"",IF(ISERROR(SUM(INDEX(Data!$V$3:$Z$114,MATCH($E15,Data!$U$3:$U$114,0),
MATCH($B15,Data!$V$1:$Z$1,0))))+
SUM(INDEX(Data!$AO$2:$AS$7,MATCH($T15,Data!$AN$2:$AN$7,0),MATCH($B15,Data!$AO$1:$AS$1,0))),"",
SUM(INDEX(Data!$V$3:$Z$114,MATCH($E15,Data!$U$3:$U$114,0),MATCH($B15,Data!$V$1:$Z$1,0))))+
SUM(INDEX(Data!$AO$2:$AS$7,MATCH($T15,Data!$AN$2:$AN$7,0),MATCH($B15,Data!$AO$1:$AS$1,0))))
So whatever value is entered into "-Dsc" or "+Fees" needs to be added and/or subtracted from this formula.
I don't know much about vba, the best I could come up with is:
Code:
Private Sub cmdclose1_Click()
Unload Me
End Sub
Private Sub cmdadd1_Click()
[Code]......
View 5 Replies
View Related
