Multiple references for one cell

[Dietcoke1953]

OK, here is the issue.

I have 37 different locations at my company that all have Safety Data Sheets. Some of the sheets will be common for more than one location i.e. WD-40, Windex...etc.

I want to make a spreadsheet where I can reference one SDS to multiple locations so any location can filter the sheet to just their location's chemicals,

I hope my explanation makes sense.

Any help is greatly appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

Tell/show us exactly how your data is laid out. That may well determine what the best approach is. My signature block below has a link for way to show small sets of sample data. You don't have to use real data if it is sensitive, just make up some that is like your data.

 

[Dietcoke1953]

Column 1 would be "Chemical Name"
Column 2 would be "Type"
Coumn 3 would be "Location"

I have 37 locations submitting lists of chemicals they store at their locations.

Want a way to enter a chemical name in Row 2, 3, 4...etc, Column 1, assign a type in Row 2, 3, 4...., Column 2, and then select the location the chemical is stored at in Row 2, 3, 4..., Column 3. This is where I'm looking for a way to assign the chemical to multiple locations, so I can then filter it to show all chemicals stored at an individual location.

For example: Lets say I have a chemical list from location "Harvey"
Their list shows they have "WD-40" at their location, so;
I input "WD-40" in Row 2, Column 1 and input it being used at location "Harvey" in Row 2, Column 3
Then as I'm going through "Houma" list, they have WD-40 too.
Instead of making another row showing "WD-40" as the Chemical, I want to be able to go back to Row 2, Column 3 and add location "Houma" to it.
Then after I complete the list with probably 200 different rows, with chemicals being stored at multiple locations, I would like to be able to filter the list by individual Location and have list show only chemicals stored at the selected location.

Thanks,

 

[Peter_SSs]

Lets try to automate it then. Try this in a copy of your workbook (or perhaps in a new workbook to test).

- Put in a few rows of data set up like in rows 2 down below. List the location names from cell D2 across. As you enter rows, mark any locations that store that chemical with an x in their columns.
- Select from A2 to the bottom right of the data you have entered so far then Insert ribbon tab -> Table -> My table has headers -> OK
- Formulas ribbon tab -> Name Manager -> Select the table (probably Table1) -> Edit -> change Name: to LocationData -> OK
- Name cell C1: Select cell C1 -> In the Name box (at the very left of the formula bar row and showing C1) place your cursor and type in Location -> Enter
- Set up Data Validation in C1: Select C1 -> Data ribbon tab -> Data Validation -> Data Validation... -> Allow: List -> Source: =OFFSET(Location,1,1,1,COUNTA(2:2)-3) -> OK (Test by now selecting one of the locations from the drop-down in C1)
- Enter the formula shown in B1. Where I have used column AZ in the formula, it needs to be further across than your table will ever go. If 37 locations, AZ should do.
- Enter the formula shown in C3. It should auto-populate to the other rows in the table.
.....................................................
- Right click the sheet name tab and choose "View Code".
- Copy and Paste the code below into the main right hand pane that opens at the previous step.
- Close the Visual Basic window & test by selecting another Location in C1. To dispaly all rows in the table, just delete any value out of C1
- Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

If you add more locations and/or chemicals, the table should automatically expand to accommodate them.
If you don't want to have a macro-enabled work book, you could stop at the dotted line and then manually filter/unfilter the location column using the drop-down in cell C2.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Location")) Is Nothing Then
    With ActiveSheet.ListObjects("LocationData").Range
      If IsEmpty(Range("Location").Value) Then
        .AutoFilter Field:=3
      Else
        .AutoFilter Field:=3, Criteria1:="x"
      End If
    End With
  End If
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		2	Houma
2	Name	Type	Location	Harvey	Houma	Loc 3	Loc 4	Loc 5
3	Chem 1		x	x	x		x
4	Chem 2		x		x
5	Chem 3			x		x		x
6	Chem 4			x
7	Chem 5		x		x
8	Chem 6							x
9
Chemical Locations

#VALUE!

</td></tr></table></td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#ffFCF9; color:#000000; "><tr><td ><b>Data Validation in Spreadsheet</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Allow</td><td >Datas</td><td >Input 1</td><td >Input 2</td></tr><tr><td >C1</td><td >List</td><td > </td><td >=OFFSET(Location,1,1,1,COUNTA(2:2)-3)</td><td > </td></tr></table></td></tr><tr><td ><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#fffcf9; color:#000000; "><tr><td ><b>Names in Formulas </b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Name</td><td >Applies to</td></tr><tr><td >C1</td><td >Location</td><td >='Chemical Locations'!$C$1</td></tr></table></td></tr></table></td></tr></table>

 

[Dietcoke1953]

Does not seem to be working, I must not have understood how to do something. When I select a location in C1 the only thing that happens is that a number appears in B1 which appears to show the position of the location in the list of locations.

Is there a way I can attach the sheet for you to troubleshoot?

Best Regards

 

[Peter_SSs]

Is there a way I can attach the sheet for you to troubleshoot?

You cannot upload actual files to the forum. You can post small screen shots & include formulas as I have done in my post above. A link in my signature block below can help with that. Alternatively, a file can be uploaded to, say, a public file-share site & a link provided here. However, I would prefer to try to resolve the issue directly in the forum if possible.

When I select a location in C1 the only thing that happens is that a number appears in B1 which appears to show the position of the location in the list of locations.

1. Are you saying that you don't get any "x" values showing down column C like in my screen shot? Did you enter the formula in cell C3 and copy it all the way down that column to the bottom of the data?

2. Check that you followed the instructions below the dotted line carefully, otherwise the code could end up in the wrong location. To check, if you open the file and right-click this sheet's tab & choose 'View Code', that Worksheet_Change code should be immediately visible.

3. What version of Excel are you using?

 

[Dietcoke1953]

Working now, I did not copy the formula down the entire column.

Thank you for all of your help.

 

[Peter_SSs]

Working now, I did not copy the formula down the entire column.

Thank you for all of your help.

Sounds like you may not have completed the second point of post 4 correctly. If you had created a formal table with that step, the formulas in column C would normally automatically populate from the one in C3. Anyway, glad it is working for you now.