Here is what I am trying to do:
I have about 50 sheets in a workbook, each for a different month. In column
A, I have an item id # listed all the way down(over 500). With each id #, i
have a current prep. date in column E, current ship date in column F, future
prep date in column H, and future ship date in column J. What I would like is
to have each of the cells with dates refer back to the previous month's page.
I have an indirect reference in C2(with the name of the sheet). Since the
period between some of the dates is more than a month, I am trying to set up
a statement saying find the id# on last month's page; if the future ship date
occurs in this month, take the date from the future ship date from last
month's page and place it in current date column for this month, otherwise
take the current date from last month's sheet for this months' page current
date.(Sorry so confusing, trying to be specifice)...please look at what I
ahve and see what my errors are. someone told me it can't be done without the
ISNA statement:

=IF(ISNA(VLOOKUP(A92,INDIRECT("'"&$C$2&"'!$b$2:$M$ 3639"),1,0)),"Not
found",IF(MONTH(TODAY())=MONTH(VLOOKUP(A92,INDIREC T("'"&$C$2&"'$B$2:$M$3639"),7,0)),
VLOOKUP(A92,INDIRECT("'"&$C$2&"'!$B$2:$M$3639"),7, 0)),VLOOKUP(A92,INDIRECT("'"&$C$2&"'!$B$2:$M$3639" ),4,0))

Thanks in advance!

Brian,

It appears that you are trying to display all "open items" on one sheet,
possibly for progress reporting or expediting or whatever, so that you have
one place to look each month, and to enable this, you are carrying forward
each open item to the next month's sheet, possibly until it is shipped. I
have noticed many people do this and I recommend against it.

Have you considered using just one sheet, with just one instance of each
item, and using filtering and/or sorting to show the open items and limit
what you see to those that are "current", which seems to mean they have prep
or ship dates in the current month? This would be a lot simpler than trying
to carry forward information from one sheet to another. It would also make
any analysis you need to do that crosses multiple months an awful lot easier.
You could monitor up to about 65000 items this way, if you ever needed to,
(and more in future versions of Excel) without archiving or deleting shipped
items.

If my suggested approach is not feasible, I can work up an approach that
does what you want, even though I recommend otherwise.

Let me know what you think and we can proceed from there.

Declan

"Brian" wrote:

Here is what I am trying to do:
I have about 50 sheets in a workbook, each for a different month. In column
A, I have an item id # listed all the way down(over 500). With each id #, i
have a current prep. date in column E, current ship date in column F, future
prep date in column H, and future ship date in column J. What I would like is
to have each of the cells with dates refer back to the previous month's page.
I have an indirect reference in C2(with the name of the sheet). Since the
period between some of the dates is more than a month, I am trying to set up
a statement saying find the id# on last month's page; if the future ship date
occurs in this month, take the date from the future ship date from last
month's page and place it in current date column for this month, otherwise
take the current date from last month's sheet for this months' page current
date.(Sorry so confusing, trying to be specifice)...please look at what I
ahve and see what my errors are. someone told me it can't be done without the
ISNA statement:

=IF(ISNA(VLOOKUP(A92,INDIRECT("'"&$C$2&"'!$b$2:$M$ 3639"),1,0)),"Not
found",IF(MONTH(TODAY())=MONTH(VLOOKUP(A92,INDIREC T("'"&$C$2&"'$B$2:$M$3639"),7,0)),
VLOOKUP(A92,INDIRECT("'"&$C$2&"'!$B$2:$M$3639"),7, 0)),VLOOKUP(A92,INDIRECT("'"&$C$2&"'!$B$2:$M$3639" ),4,0))

Thanks in advance!