1

I have an Excel workbook with numerous sheets entitled "Week1", "Week2", "Week3" ... "Week7". Each week, I input the customers IDs of people buying my product in column C, and -- as I am interested knowing if these are return customers or if they are new ones -- I have written a function to check if the customers I input in Week2-Week7 are new customers or whether they have previously bought my product.

To that end, I have developed the following formula which I input into column F for Week2:

=IF(COUNTIF(Week1!$C$3:$C$100,C3)>=1,"Old","New")

With the relevant part of "Week1" looking like:

_|-----C-----|-----F-----|

3|  ValPot1  |  *blank*  |

With the relevant part of "Week2" looking like:

_|-----C-----|-----F-----|

3|  ValPot1  |    Old    |

Now, although this works well, it becomes rather cumbersome by the Week7, when I have to use the following function:

=IF(OR(COUNTIF(Week1!$C$3:$C$100,C3)>=1,COUNTIF(Week2!$C$3:$C$100,C3)>=1,COUNTIF(Week3!$C$3:$C$100,C3)>=1,COUNTIF(Week4!$C$3:$C$10>0,C3)>=1,COUNTIF(Week5!$C$3:$C$100,C3)>=1,COUNTIF(Week6!$C$3:$C$100,C3)>=1),"Old","New")

Consequently, I've been trying (unsuccessfully) to use the following VBA function:

Function SHEETOFFSET(offset, Ref)

'   Returns cell contents at Ref, in sheet offset

    Application.Volatile

    With Application.Caller.Parent

        SHEETOFFSET = .Parent.Sheets(.Index + offset) _

         .Range(Ref.Address).Value

    End With

End Function

By incorporating it in sheet "Week2", cell F3, in the following manner:

=IF(COUNTIF(SHEETOFFSET(-1,$C$3):SHEETOFFSET(-1,$C$100),C3)>=1,"Old","New")

In effect, I'm just replacing my previous worksheet reference (of Week1!$C$3:$C$100) with my new SHEETOFFSET function....

Unfortunately, this doesn't work, and simply returns a #VALUE! error. What am I doing wrong, and how can I fix my function?

2

I would reorganise the data into a single sheet, with an additional Week column. Then you can use your original formula, replacing the Week1 reference with a reference to the single sheet.

This will have too many other benefits to list, making your file so much easier to use, maintain, format and analyze.

  • 2
    +1 -- it's easier and saves much, much time and headache down the line to use proper data architecture (raw data in one sheet, reporting in other sheets), than to develop and maintain functionality to coax information out of bad data architecture. – teylyn May 21 '14 at 8:36
0

You are on the right lines with your UDF, but I would change the logic slightly.

I would give it the range to look in, and the value to look for, and use the name of the sheet it is called from to decide which other sheets to look at.

Function CheckCustomer(offset, Ref)

Dim InitialSheet As String
Dim WeekNum As Long
Dim SheetLoop As Long

InitialSheet = Application.Caller.Parent.Name 
'know where the function is called from
WeekNum = Val(Mid(InitialSheet, 5)) - 1
' week to start searching from is one less than current sheet name

For SheetLoop = 1 To WeekNum
    If WorksheetFunction.CountIf(Sheet("Week" & SheetLoop).Range(offset.address), Ref.Value) > 0 Then
        'Found name - return true, and stop looking for more
        CheckCustomer = True
        Exit Function
    End If
Next
'all searches didn't find value, so new customer
CheckCustomer = False

End Function

To use in your IF function, you would call it like this:

=IF(CheckCustomer($C$3:$C$100,C3),"Old","New")

The function will search all previous weeks, and let you know if the customer is new (FALSE) or old (TRUE)

0

why not simply using a function that searches the customer ID in all worksheets, and if it's found more than one time, will return "Old Client" ?

Function newclient(clientID As String) As String
Dim count As Integer

For Each Worksheet In Worksheets
    If Not Worksheet.UsedRange.Find(clientID, lookat:=xlWhole) Is Nothing Then
      count = count + 1
      MsgBox (count)
    End If
Next Worksheet

If count > 1 Then
  newclient = "Old client"
Else

newclient = "New client"
End If

End Function

Have a nice day!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.