# Trouble using Excel OFFSET function

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) _

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?

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.

• +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

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)

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!