Trouble using Excel VBA 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:

Please Login or Register  to view this content.

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?

Here's my take on that idea...

Please Login or Register  to view this content.

It's used in a cell exactly like COUNTIF to get a numeric answer, but it counts the values on every sheet PRIOR to the sheet where the formula exists. For example:

=CountIfPriorSheets(A1:A20, "Cat")


Now, you can wrap that in your ealier test for a "old/new" response:

=IF(CountIfPriorSheets(A1:A20, "Cat")>0, "Old", "New")

Here's a link to the published code and a sample workbook.

I think your life would be much easier if you kept all the data on one sheet and added a column to indicate week number. The formulas would then be pretty simple.