Vlookup and If

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I am currently trying to summarize all the totals by name
in excel so say Bob sold 6 units in sheet 1 and then sold
7 units in sheet 2, but sold none in sheet 3,nor was he
mentioned in sheet 3, how could i make it ignore that
there was nothing sold in that sheet? I am setting the
formula up as =vlookup(sellers name, range,collum,false)
+vlookup(sellers name, range,collum,false). Any help would
be greatly appreciated, i'm trying to keep the formulas
consistant for each buyer.
 
If you could use a smart way of naming the sheets with a name and a number
just like the default sheet names you could use

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(1:3)&"'!A2:A50"),"Bob",INDIRECT("'Sh
eet"&ROW(1:3)&"'!B2:B50")))

where the sales people's names are in A2:A50 and the sales in B2:B50, better
would be

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(1:3)&"'!A2:A50"),C1,INDIRECT("'Sheet
"&ROW(1:3)&"'!B2:B50")))

where C1 holds the sales person's name

otherwise you have to test each range like

=IF(ISNUMBER(MATCH(seller_name,Sheet1!A2:A50,0)),VLOOKUP(seller_name,Sheet1!
A2:B50,2,0),0)+and so on

as you can see, one sheet formula is as long
 
I have a column in sheet(1) which is set to have a numeric datatype.
This column may or may not have an entry (some entries may be a value of zero).

In sheet(2) I'm using a cell reference to this column.
I'm using the formula =IF(ISBLANK(cellref),"",cellref) but this creates a problem in that it no
longer is a numeric datatype. In otherwords, I need the cell in sheet(2) to contain either a value,
zero, or remain blank depending on what is in the referenced cell in sheet(1). Reason being is, if
the cells in sheet(2) contain both numerals and text then the Access Import Wizard will choose the
text datatype for this field. I'm finding this a problem with both my numeric and date fields.

Is there another way to use an IF statement where it will do nothing if TRUE?

-B
 
Back
Top