# Formula that will return a T/F value for every sheet in a string of ~25

1. ## Formula that will return a T/F value for every sheet in a string of ~25

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!

2. ## Re: Formula that will return a T/F value for every sheet in a string of ~25

It would be really helpful if you uploaded a sample workbook to better understand this issue. Make sure to sanitize if for confidential data. Also, a sample with expected results would be beneficial.

3. ## Re: Formula that will return a T/F value for every sheet in a string of ~25

Can do, give me a minute.

4. ## Re: Formula that will return a T/F value for every sheet in a string of ~25

Here's the de-identified sheet. The sheets labeled W# are easy enough to do by hand and are in numerical order, but the DNA sheets R, FC, GGGT, etc have a ton of different values. I also included a sheet where I have written out what I have played with thus far. Sorry it's not much, I'm new to messing with excel formulas. Let me know if you need anything else.

5. ## Re: Formula that will return a T/F value for every sheet in a string of ~25

A thought, not sure if this would help, is that the find all feature returns all the data I want, I just don't know how to put that in cell format. Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1