Reference to a specific worksheet in Excel formulas

[Monty Norman]

Hello Excelers,

Here is the situation:

We have daily worksheets that contain daily transactions.

I need a formula that would check if a transaction appeared before.

So I have a XLOOKUP formula that looks something like this:

=IF(ISNUMBER(XLOOKUP($A2,'Data (06-05)'!A:A,'Data (06-05)'!I:I,"---x---",FALSE)),"Old","")

Now this was looking at yesterday's worksheet data (06-05) = June 5th.

Tomorrow this formula would be:

=IF(ISNUMBER(XLOOKUP($A2,'Data (06-06)'!A:A,'Data (06-06)'!I:I,"---x---",FALSE)),"Old","")

How can I make this formula more dynamic and have it look at the correct worksheet(s)?

I can tell you that the worksheet order makes that worksheet I need to look for the one to the Right of the worksheet I have the formula on.
So the worksheet "Data" (the current's day data) is Index x, and the prior day's data is on sheet index x+1 (To the right of the "Data" sheet if that helps or if we can use Sheet Index in some way?!

How can I make the formula look past the yesterday's worksheet, so for example look all the way to the beginning of the month and maybe count how many days ago this transaction first appeared?

Thanks

Monty

 

[awoohaw]

I am loath to use the indirect function, but you can build your worksheet name and cell references as text, then wrap that in the indirect function.

INDIRECT("'" & Text(WorkDay.Intl(Today(),-1,1,Holidays),"mm-dd")&"'!A1:A500"))

This is typed freehand and not in a worksheet, i can't guarantee accuracy of the arguments. but I hope you understand what I wrote.