Mr Excel & excelisfun Trick 41: VBA or Formula Extract Prefix for VLOOKUP

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

So I used Excel Magic Trick 185 to dynamically pull content from a spreadsheet: http://www.youtube.com/watch?v=6bGKhbUYOas

However,
It is only working for a few of my columns though and I am getting reference errors, http://www.mediafire.com/?gje8srbp8x2j48w

Can anyone check into my spreadsheet and help me understand why I get these ref errors? Is it because I have spaces in the column names or within the cells? I know a macro is probably a better way to do this, or an auto-filter, but I need it to dynamically update. Any ideas on how to fix this problem or a better way to solve it?

Thanks!

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael

Hi All,

I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this?? Thanks a bunch in advance.

SD

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

I have a table like below
A B
1 x1234 value
2 y1234 value


If I want to look for all values that start with "x", is there a way to do it within a vlookup formula? Something like:

vlookup("x*", A1:B2, 2, False)

I know there's other ways to do this, but I want it all contained in one formula instead of splitting the A column using LEFT(A1, 1).

Here is what I am trying to do. I have test cases that I need to monitor for actual date action taken and the calculated date action needs to be taken. I have a summary tab that lists the test case and i have one column that will look at the actual dates work performed tab and return the last cell with data. What I need to do now is for the same test look to my calculated date completition tab and return the next value in after the last action date from the actual date page ( do if you will, the next action due based on the last entered action (actual tab). You will see that the columns have numbers on top that represent the days between actions. I need to return, as the next action date from the calculated date tab based on the last action date that had an entry on my actual date tab.

I hope this makes sense, but if not let me know and I will try to provide a more concrete example.

Calculated due date

  C D E F G H I J K L M 3 Days to next action       30 30 24 18 52 18 48 4 Name type inv num date date entered action 1 action 2 action 3 action 4 action 5 action 6 5 test 1 a 406448 13-Dec-2010 12-Jan-2011 11-Feb-2011 7-Mar-2011 25-Mar-2011 16-May-2011 3-Jun-2011 21-Jul-2011 6 test 2 b 413251 25-Feb-2011 27-Mar-2011 26-Apr-2011 20-May-2011 7-Jun-2011 29-Jul-2011 16-Aug-2011 3-Oct-2011 7 test 3 c 408410 11-Jul-2010 10-Aug-2010 9-Sep-2010 3-Oct-2010 21-Oct-2010 12-Dec-2010 30-Dec-2010 16-Feb-2011 8 test 4 d 413600 17-Jan-2011 16-Feb-2011 18-Mar-2011 11-Apr-2011 29-Apr-2011 20-Jun-2011 8-Jul-2011 25-Aug-2011 9 test 5 e 414017 10-Feb-2011 12-Mar-2011 11-Apr-2011 5-May-2011 23-May-2011 14-Jul-2011 1-Aug-2011 18-Sep-2011
Spreadsheet Formulas Cell Formula E5 =IF('Actual date of action'!C5="","",'Actual date of action'!C5) F5 =IF('Actual date of action'!D5="","",'Actual date of action'!D5) G5 =IF($F5="","",$F5+$G$3) H5 =IF($F5="","",$G5+$H$3) I5 =IF($F5="","",$H5+I$3) J5 =IF($F5="","",$I5+J$3) K5 =IF($F5="","",$J5+K$3) L5 =IF($F5="","",$K5+L$3) M5 =IF($F5="","",$L5+M$3) E6 =IF('Actual date of action'!C6="","",'Actual date of action'!C6) F6 =IF('Actual date of action'!D6="","",'Actual date of action'!D6) G6 =IF($F6="","",$F6+$G$3) H6 =IF($F6="","",$G6+$H$3) I6 =IF($F6="","",$H6+I$3) J6 =IF($F6="","",$I6+J$3) K6 =IF($F6="","",$J6+K$3) L6 =IF($F6="","",$K6+L$3) M6 =IF($F6="","",$L6+M$3) E7 =IF('Actual date of action'!C7="","",'Actual date of action'!C7) F7 =IF('Actual date of action'!D7="","",'Actual date of action'!D7) G7 =IF($F7="","",$F7+$G$3) H7 =IF($F7="","",$G7+$H$3) I7 =IF($F7="","",$H7+I$3) J7 =IF($F7="","",$I7+J$3) K7 =IF($F7="","",$J7+K$3) L7 =IF($F7="","",$K7+L$3) M7 =IF($F7="","",$L7+M$3) E8 =IF('Actual date of action'!C8="","",'Actual date of action'!C8) F8 =IF('Actual date of action'!D8="","",'Actual date of action'!D8) G8 =IF($F8="","",$F8+$G$3) H8 =IF($F8="","",$G8+$H$3) I8 =IF($F8="","",$H8+I$3) J8 =IF($F8="","",$I8+J$3) K8 =IF($F8="","",$J8+K$3) L8 =IF($F8="","",$K8+L$3) M8 =IF($F8="","",$L8+M$3) E9 =IF('Actual date of action'!C9="","",'Actual date of action'!C9) F9 =IF('Actual date of action'!D9="","",'Actual date of action'!D9) G9 =IF($F9="","",$F9+$G$3) H9 =IF($F9="","",$G9+$H$3) I9 =IF($F9="","",$H9+I$3) J9 =IF($F9="","",$I9+J$3) K9 =IF($F9="","",$J9+K$3) L9 =IF($F9="","",$K9+L$3) M9 =IF($F9="","",$L9+M$3)

Excel tables to the web >> Excel Jeanie HTML 4

Hi everyone:

I have been trying to come up with a formula (but wit not avail -that's why i'm here ) that can do what i'm going to explain next.

Ok, basically what i'm trying to do is to get the number of times a value repeats in a row in a given column range. I guess many of you (the experts) are thinking on the COUNTIF function but that fucntion alone wont work for my purpose -otherwise i wouldn't be here .

Here it is the scenario:

Coulmn Range $B1:$B5000
X
X
X
Y
X
X
Y
Y
Y
X
X
-----
2