I am looking for a way to look across multiple sheets for a given value and when found return the date in the same row as that value. In the sample file I have uploaded, on the Schedule page, the Date Sent formula needs to check all previous months for the R.O. number in column A and, if found, return that R.O.'s date in column M. I also need to transfer any comments in the same manner.
Hello,
I'm not sure if this may work, but can't you do an OR function within a vlookup?
That way you would look for a value in SheetA or SheetB or sheetC. I think I already did it before.
Kind Regards,
FCarv
If you list the sheetnames in an area say, in L2:L4 of Schedule, then you can try:
=VLOOKUP($A8+0,INDIRECT("'"&INDEX($L$2:$L$4,MATCH(TRUE,COUNTIF(INDIRECT("'"&$L$2:$L$4&"'!A1:A1000"),$A8)>0,0))&"'!A1:N1000"),13,0)
confirmed with CTRL+SHIFT+ENTER and copied down.
In next column, similarly:
=VLOOKUP($A8+0,INDIRECT("'"&INDEX($L$2:$L$4,MATCH(TRUE,COUNTIF(INDIRECT("'"&$L$2:$L$4&"'!A1:A1000"),$A8)>0,0))&"'!A1:N1000"),13,0)
confirmed with CTRL+SHIFT+ENTER and copied down.
Then select the column and format the cells as Custom with type: 0;-0;;@ to hide the 0's
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks