Hi, first time poster here. I'm currently working on an inventory for biological samples identifiable by a 6 number code. I have ~25 pages of box maps (where the sample is in each box), and one primary inventory sheet where the location (box) of each sample is reported along with some other information. Thus far I have been querying the sheets and listing the locations manually, but as the number of samples has grown to over 2k, this is getting rather tedious.
What I want is a function that will query all of the box map pages and return to me the name of the box (listed at the top of each page), or the name of the sheet (1 sheet per box).
What I have been using thus far is =IF(COUNTIF('R1'!B4:U23,10165),"R1",""). R1 is the name of this particular sheet, but all of the tables are in the same location (B4:U23). 10165 is the sample number. Is there any way to string this statement together with others like it so that I can report a list of the sheets where the sample is located? Or would I have to query each individually.
Additionally, I thought about trying to nest IF statements to return multiple values, but I don't think that would really work in this case being as I don't want the formula to stop looping whenever it finds one location. Some of these samples have upward toward 10 separate locations. I plan on scouring the internet for more possible solutions, but if anyone has any suggestions, I would very much appreciate it.
Thanks!
Bookmarks