Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Lookup
On a test sheet, I have this formula in A1: =IF(Import!M2="BIC USA Inc.",IF(ISNUMBER(Import!A2+0),Import!A1+ROW()/10^10,""),"") The above formula is copied down to A4000. It pulls in any item number that is associated with "BIC USA Inc." into the corresponding row on the test sheet. On that same sheet, I have this formula in B1: =IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0))) The above formula is copied down to B4000. It removes the empty rows in column A so that the first returned value appears in B1, then B2, B3, etc. This works great if both of these formulas start in row 1, however, my problem is that the sheet that I actually want to use this on has data in A1:B18. So I am trying to begin my formulas in A19 and B19, but I am having no luck (even if I try to modify them a little). Can anyone help? Thanks, Chad |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Lookup
begin my formulas in A19 and B19,
=IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(SM ALL(A:A,ROW()),A:A,0))) Replace this: =IF(ROW()COUNT(A:A) With this: =IF(ROWS(B$19:B19)COUNT(A:A) -- Biff Microsoft Excel MVP "Chad F" wrote in message ... On a test sheet, I have this formula in A1: =IF(Import!M2="BIC USA Inc.",IF(ISNUMBER(Import!A2+0),Import!A1+ROW()/10^10,""),"") The above formula is copied down to A4000. It pulls in any item number that is associated with "BIC USA Inc." into the corresponding row on the test sheet. On that same sheet, I have this formula in B1: =IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0))) The above formula is copied down to B4000. It removes the empty rows in column A so that the first returned value appears in B1, then B2, B3, etc. This works great if both of these formulas start in row 1, however, my problem is that the sheet that I actually want to use this on has data in A1:B18. So I am trying to begin my formulas in A19 and B19, but I am having no luck (even if I try to modify them a little). Can anyone help? Thanks, Chad |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Lookup
T. Valko wrote:
begin my formulas in A19 and B19, =IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0))) Replace this: =IF(ROW()COUNT(A:A) With this: =IF(ROWS(B$19:B19)COUNT(A:A) Or use ROWS($1:1). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Lookup
Chad,
These adjustments should work fine In A19: =IF(Import!M2="BIC USA Inc.",IF(ISNUMBER(Import!A2+0),Import!A1+ROW()/10^10,""),"") In B19: =IF(ROWS($1:1)COUNT($A$19:$A$4000),"",INDEX(Impor t!A:A,MATCH(SMALL($A$19:$A$4000,ROWS($1:1)),$A$19: $A$4000,0)+1)) Copy A19:B19 down -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Chad F" wrote: On a test sheet, I have this formula in A1: =IF(Import!M2="BIC USA Inc.",IF(ISNUMBER(Import!A2+0),Import!A1+ROW()/10^10,""),"") The above formula is copied down to A4000. It pulls in any item number that is associated with "BIC USA Inc." into the corresponding row on the test sheet. On that same sheet, I have this formula in B1: =IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0))) The above formula is copied down to B4000. It removes the empty rows in column A so that the first returned value appears in B1, then B2, B3, etc. This works great if both of these formulas start in row 1, however, my problem is that the sheet that I actually want to use this on has data in A1:B18. So I am trying to begin my formulas in A19 and B19, but I am having no luck (even if I try to modify them a little). Can anyone help? Thanks, Chad |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Lookup
Ooops!
I missed the other instance or ROW() that also needs to be changed. Here's the whole thing: =IF(ROWS(B$19:B19)COUNT(A:A),"",INDEX(Import!A:A, MATCH(SMALL(A:A,ROWS(B$19:B19)),A:A,0))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... begin my formulas in A19 and B19, =IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(S MALL(A:A,ROW()),A:A,0))) Replace this: =IF(ROW()COUNT(A:A) With this: =IF(ROWS(B$19:B19)COUNT(A:A) -- Biff Microsoft Excel MVP "Chad F" wrote in message ... On a test sheet, I have this formula in A1: =IF(Import!M2="BIC USA Inc.",IF(ISNUMBER(Import!A2+0),Import!A1+ROW()/10^10,""),"") The above formula is copied down to A4000. It pulls in any item number that is associated with "BIC USA Inc." into the corresponding row on the test sheet. On that same sheet, I have this formula in B1: =IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0))) The above formula is copied down to B4000. It removes the empty rows in column A so that the first returned value appears in B1, then B2, B3, etc. This works great if both of these formulas start in row 1, however, my problem is that the sheet that I actually want to use this on has data in A1:B18. So I am trying to begin my formulas in A19 and B19, but I am having no luck (even if I try to modify them a little). Can anyone help? Thanks, Chad |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vendor Lookup
Or use ROWS($1:1).
Yeah, but if an entry is made anywhere on row 1 that formula will recalculate. It's better to use *specific cell references*. -- Biff Microsoft Excel MVP "Glenn" wrote in message ... T. Valko wrote: begin my formulas in A19 and B19, =IF(ROW()COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMA LL(A:A,ROW()),A:A,0))) Replace this: =IF(ROW()COUNT(A:A) With this: =IF(ROWS(B$19:B19)COUNT(A:A) Or use ROWS($1:1). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vendor Name Lookup | Excel Worksheet Functions | |||
Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column of other worksheet | Excel Worksheet Functions | |||
Add numbers belonging to a vendor | Excel Discussion (Misc queries) | |||
Creating a Vendor ID from Vendor Name... | Excel Worksheet Functions | |||
How do I set up a way to keep track of vendor invoices? | Excel Worksheet Functions |