+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    New Orleans
    MS-Off Ver
    Excel 2007
    Posts
    5

    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. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,255

    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.
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-04-2013
    Location
    New Orleans
    MS-Off Ver
    Excel 2007
    Posts
    5

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

    Can do, give me a minute.

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    New Orleans
    MS-Off Ver
    Excel 2007
    Posts
    5

    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.

    Sample Inventory for upload.xlsx

  5. #5
    Registered User
    Join Date
    04-04-2013
    Location
    New Orleans
    MS-Off Ver
    Excel 2007
    Posts
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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