Hi,
I work in a laboratory, and we have various freezers named: Fz1, Fz2, ..... Fz9, Fz10, Fz11, Fz12
We keep various samples in these freezers such as blood.
I have created a large spreadsheet which links each sample type to the freezers which store them. (The table also has more columns with more information I have kept out)
See below a very basic example
Saliva Fr2; Fr6
Urine Fr1
Plasma Fr1; Fr12
Serum Fr8; Fr11; Fr12
I have created another tab with a table of sample types going down the page and freezers going across.
I want the cells to link back to the tab described above, highlighting the cells which show which freezers contain which samples, as shown in the image.
The cells have a formula =ISNUMBER(SEARCH(B$1,Freezer!$A$1)) where Freezer is the other tab containing the table
Freezer example.PNG
However the issue is this says Freezer Fr1 contains Serum which isn't the case.
I understand this is because Fr11 and Fr12 contain "Fr1" in them.
I have tried ways of editing this, however, any solution I have found doesn't work as there are other freezer names in the original cell it's pulling from. I also want to keep the Substring as the cell rather than the text, as we have many freezers.
Bookmarks