Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.
What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).
I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?
Thanks,
Peter Fraga
(fragapete@hotmail.com)
Hi All,
I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw
Dynamic sub tables based on a master sheet array formula
The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))
B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.
What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.
I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.
My question is...does anyone have any hints how to optimize this formula?
Thanks
Tony
Hi, glad to find this forum which allows me to post an image to convey my predicament.
So this is my problem:
I have been unsuccessful using INDEX and MATCH in this case. I am looking for a correct syntax to INDEX and MATCH, or an alternative function, that will help me arrive at the desired outcome.
Any help will be appreciated.
Thanks a million.
Hi,
I know the LOOKUP function in Excel is provided for backward compatibility. But isn't it a lot easier to use this function for left lookups instead of the Index/Match combination?
See attached sheet. Just wondering ... Does LOOKUP still exist in XL 2007?
cheers
Teylyn
I am trying to lookup pairs of months in a table, then depending on what month pairs a user types in it will return the value from the table under the month pair in the table.
See Attached Picture.
The blue arrows represent where I want the values to go.
For example,
The user enters "feb" in A7 and "may" in A8. I want th then look through all the month pairs in the table above (B2:E3). It then recoginizes that B2&B3 Match, so it inputs 1.23 in cell B8.
Your help is greatly appreciated.
I'm at my wits end trying to figure out what is wrong! I'm new at excel vba and new at forums so please have patience!
I keep receiving Run Time Error "Could Not Set The Value Property:Type Mismatch" in the last line of code. My Input values show up fine but it seems the Index is not looking up the values.
This works fine in the worksheet:
=(INDEX(B2:M53,MATCH(Q17,B1:M1,0),MATCH(Q18,A2:A53,0)))
Private Sub txtUiMulti_Exit(ByVal cancel As MSForms.ReturnBoolean)
'Returns United Inch multiplier from UI Table
Dim Input1 As Long
Dim Input2 As Integer
Dim ws As Worksheet
Set ws = Worksheets("UnitedInch")
Worksheets("UnitedInch").Activate
'Gets the frame width from userform, compares to top row of table
Input1 = txtFrameWidth1.Value
'Gets total united inches from userform, compares to 1st column of table
Input2 = txtUnitedInchTotal.Value
'Should return a value from A2:M53
txtUiMulti.Value = Application.index(("B2:M53"), Application.Match(Input1, ("B1:M1"), 0), Application.Match(Input2, ("A2:A53"), 0))
End Sub
Here is a sample of the table it runs 53 rows:
0.5
1
1.5
2
2.5
3
3.5
4
4.5
5
5.5
6
12
3.00
3.00
3.67
4.00
4.00
4.67
5.00
5.00
5.67
6.00
6.00
6.67
14
3.00
3.67
4.00
4.00
4.67
5.00
5.00
5.67
6.00
6.00
6.67
7.00
16
3.67
4.00
4.00
4.67
5.00
5.00
5.67
6.00
6.00
6.67
7.00
7.00
18
4.00
4.00
4.67
5.00
5.00
5.67
6.00
6.00
6.67
7.00
7.00
7.67
20
4.00
4.67
5.00
5.00
5.67
6.00
6.00
6.67
7.00
7.00
7.67
8.00
22
4.67
5.00
5.00
5.67
6.00
6.00
6.67
7.00
7.00
7.67
8.00
8.00
I have an excel problem. I need to track training event currency for a training program. Table looks like this:
1Jan 2Jan 3Jan 4Jan
Jane p o l
Andy p o p l
Joe o p l o
The p-o-l are different training programs. I need a table that will return the last date the person did the training. I wrote a index-match function that does this very well. The problem is that when I make the value dicrete by putting a "0" at the end of the MATCH function excel searches the table from left to right and stops at the first occurance of the date. So for training "p" it will return 1Jan when it should return 3Jan for Andy. How do I get it to look right to left?
Hi...so I have this array with dates comprising the top row and accounts comprising the left most row....I need to match with the account numbers and I can't figure out how to get around the account names in front of the account numbers (and no, multiple columns is not a possible solution in this scenario)
John Doe (1111)
Jim Smith (2222)
Thanks
Nick
Afternoon all,
I was wondering if anyone could help me with the following formula:
Code:
=INDEX(Calc,MATCH(RIGHT(U11,4),Cost_Centre,0),MATCH(LEFT(U11,2),Trade_Class,0))/12*1*I11
I don't seem to be getting the desired result, the value returned is #N/A when it should be 4%.
Thanks
