EXCEL Formula Question 2

[Nullsweat]

Hola

I am trying to pull in some data via a web query. However the query gives associate names and units. I then want to differentiate users in a receive function vs any other function. I use a VLOOKUP for that. Here is where my problem starts. As my web query wont always be the same size I have to have(???) the VLOOKUP extend past the data in the column. This gives a #VALUE! error. I then want a sum of just the people who work in receive but not the #VALUE!'s. I tried a SUMIF but do not know how to tell it to look for " "??
If I SUM the Units column, I get #VALUE!.....
Thanks in advance
Sean

Here is an example -
Name Units TOTAL FOR HOUR
Sean 20 =SUMIF(????)
Sam 30
#VALUE!
#VALUE!

 

[Zathras]

Assuming that the cells in the left-hand column (e.g.,"A&quot are blank when the right-hand column (e.g.,"B&quot has #VALUE! then this formula could do the job:

  =SUMIF(A2:A100,">""""",B2:B100)

Change the row numbers to fit your application.

 

[RobBroekhuis]

Or, perhaps a little more cleanly, you can put the if in column B:

=if(A1="","",vlookup(...))

That way the #VALUE!s won't show up.
Rob

 

[SkipVought]

Hi,
The bane of spreadsheet function used on variable length tables is that...

1. you've got these formulas that you have propogated how far down??? and
2. those cells are NOT EMPTY and
3. you have to add more rows of fromulas if the table exceeds your current number or formula rows

just to name a few nagging problems.

BUT if you were to create the formulas as they were needed based on the Worksheet_Change event. it just might eliminate most if not all of those probelms.

Here's an example...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    If bChange Then Exit Sub
    bChange = True
    For Each t In Target
        With t
            If .Column = 1 Then
                Cells(.Row, 2).Formula = "=vlookup(A" & .Row & ",$C$2:$Z$999,4,FALSE)"
            End If
        End With
    Next
    bChange = False
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Nullsweat]

Thanks for all the help..

Zathras and Rob especially. I am sorry Skip but I am not proficient enough in VBA yet to use something like that. I can almost follow the code, but as of yet, have not used any variables. I do most of mine with the Macro Recorder. <sheepish grin>
But I do appreciate the help
Sean