G
Guest
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,INDIRECT("'"&$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!
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,INDIRECT("'"&$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!