Problemas ao usar a função OFFSET do Excel

Question

Eu tenho uma pasta de trabalho do Excel com várias folhas intitulada "Week1", "Week2", "Week3" ... "Week7". A cada semana, eu insiro os IDs de clientes de pessoas que compram meu produto na coluna C e - como estou interessado em saber se são clientes de retorno ou se são novos - escrevi uma função para verificar se os clientes que insumo na Semana2-Semana7 são novos clientes ou se compraram anteriormente o meu produto.

Para esse fim, desenvolvi a seguinte fórmula que insiro na coluna F da Semana 2:

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

Com a parte relevante de "Week1" semelhante a:

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

3|  ValPot1  |  *blank*  |

Com a parte relevante de "Semana2" parecida com:

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

3|  ValPot1  |    Old    |

Agora, embora isso funcione bem, torna-se um pouco complicado na semana 7, quando eu tenho que usar a seguinte função:

=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")

Consequentemente, tenho tentado (sem sucesso) usar a seguinte função do VBA:

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

Incorporando-o na planilha "Semana2", célula F3 , da seguinte maneira:

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

Na verdade, estou apenas substituindo minha referência de planilha anterior (de Week1!$C$3:$C$100 ) por minha nova função SHEETOFFSET ....

Infelizmente, isso não funciona e simplesmente retorna um erro #VALUE! . O que estou fazendo de errado e como posso consertar minha função?

    

Answer 1

Eu reorganizaria os dados em uma única folha, com uma coluna Semana adicional. Em seguida, você pode usar sua fórmula original, substituindo a referência Week1 por uma referência à folha individual.

Isso terá muitos outros benefícios para listar, tornando seu arquivo muito mais fácil de usar, manter, formatar e analisar.

    

Answer 2

Você está na linha certa com a sua UDF, mas eu mudaria um pouco a lógica.

Eu daria a ele o intervalo para procurar e o valor a ser procurado e usar o nome da planilha de onde é chamado para decidir em quais outras planilhas olhar.

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

Para usar em sua função IF , chame assim:

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

A função pesquisará todas as semanas anteriores e informará se o cliente é novo (FALSE) ou antigo (VERDADEIRO)

    

Answer 3

por que não usar simplesmente uma função que pesquisa o ID do cliente em todas as planilhas e, se for encontrada mais de uma vez, retornará "Cliente antigo"?

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

Tenha um bom dia!