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
I'm stuck and could really use some help.
I've just discovered the power of the SumProduct function and I'm trying to use it to analyze a range A2:A10. One and only one cell in A2:A10 will contain text, and I'm trying to get SumProduct to return the text.
I thought it would be simple till I read the Excel help file. It says that SumProduct treats array entries that are not numeric as if they were zeros.
Is there a trick to fool Excel into doing what I need? Thanks!
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
Hi. I would like to perform vlookup over several columns.
f
a 1 5 e 9 13
b 2 6 f 10 14
c 3 7 g 11 15
d 4 8 h 12 16
My intent is to lookup A1="f",
over the table of B2:G5
to obtain the offset values associated with "f", namely 10, 14,...
The simplest way is to move "e through h" below "d" and perform a regular vlookup, but I prefer not doing so because they have different properties and I intent to expand each column in the future.
Thanks for the headsup.
Regards,
csw
Hi
I'll probably get knocked over in the rush here!
I saw a spreadsheet this morning that had hidden rows & cloumns, quite normal really, but this sheet had expand & compress '+' & '-' buttons outside the cell reference bars, much like you see in Windows Explorer, these enabled you to quickly hide/unhide what I'm assuming are pre-determined sections of the sheet.
How is this done?
Rossco
"Excelover Exceloser"
Hi everybody,
I have 7 lookup values on each row from columns Z:AF, and I want the corresponding value of Column A of File BBL220.xls that are on column C and D of that same file. I was able to do it like this [The sum of each corresponding value on one column at a time]:
=(SUMPRODUCT(--(ISNUMBER(MATCH([BBL220.xls]Jun25!$A$8:$A$110,Z24:AF24,0))),INDEX([BBL220.xls]Jun25!$A$8:$D$110,,3))+SUMPRODUCT(--(ISNUMBER(MATCH([BBL220.xls]Jun25!$A$8:$A$110,Z24:AF24,0))),INDEX([BBL220.xls]Jun25!$A$8:$D$110,,4)))/2000
However, this is when the file is opened, but the file is so well buried in directories that when I close the file, the formula is just a mess and is really huge. I am affraid that as the file gets even more buried in years to come, this formula may exceed the character limit.
Any ideas on how to add column C & D without having a formula for each? I know I can cut it but I prefer everything on one cell.
Thanks a ton
I have rows of text that I want to break after the first space in the text and only the first space. I don't see how to do this outside of a special macro and I can't write VBA... ideas?
Hi guys,
I have 3 coloumns of data on 2 sheets.
On the first sheet I would like to add a 4th saying TRUE if the row of data is replicated on the other sheet, false if it doesn't match anywhere on the other sheet.
Attached is a basic example.
Thanks in advance.
Simon
