Referencing

[cannotunderstandexcel]

Hi, I’m not sure what this is called so it’s hard to search for it but what I need help with is..

We have a list of point IDs e.g B1050, B1052, etc. which are devices on a fire alarm system, and we have a list of fire zones and we need to create a sheet which has the point IDs along the top and the list of fire zones along the side and we need to mark/tick which zones are triggered when a point is activated, e.g. if point B1050 goes in to alarm then fire zones 15, 16 and 17 are triggered

I have all the points and I know which zone trigger for each point, but how do I get excel to read my data so I end up with a chart with ticks in the right places?

At the moment we are having to manually cross reference the information and manually enter the marks in the right places

 

[Peter_SSs]

A couple of things might help get some answers.

1. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. We have no idea what your data looks like and how it is laid out. Can you give us a small sample of the data so that we can see the layout and then explain how it should work in relation to that sample data? For helpers to be able to work with your sample data to test their ideas, they need to be able to copy your sample data. To help with that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
   
   Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[cannotunderstandexcel]

Hi Peter, thank you for your reply. I have updated my profile as suggested. I tried uploading a mini sheet, but have had trouble getting the button to activate. I have added an image of some of the data if that helps at all?

Within the orange area (fire dampers), it shows the point ID and the zones that are triggered, and down the first column it shows the detection zones. I would like to know if I can somehow get it to enter something in the correct detection zone for the corresponding points - I appreciate I would need to have the data for the trigger zones in a better format for it to be able to read it

 

[Peter_SSs]

but have had trouble getting the button to activate.

Did you look at the 'Known XL2BB Issues' right near the top of the article?

 

[cannotunderstandexcel]

Sample Data.xlsx
	A	B	C	D	E	F	G	H																						AD	AE	AF	AG	AH								AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
2					ALARM ZONES																																										Fire Dampers
3					AZ1,2,3,5,10,48,49,54,51,52,53	AZ6 OUTPATIENTS STAIRS	AZ7 LIFTSHAFT	AZ8 GND FLR OUTPATIENTS																						32 1ST FLR OUTPATIENTS	33 1ST FLR MAIN CORRIDOR	34 1ST FLR INPATIENTS	35 1ST FLR INPATIENTS	36 1ST FLR INPATIENTS								43,44,45,46,47 MAIN PLANTRM	50 GND FLR GP1	55,56,57,58,PLANTROOMS	GENERATOR HOUSE		B4088 DAMPER ZONE 39	B5002 DAMPER Z44	B5004 DAMPER Z44	B5006 DAMPER Z38/44	B5008 DAMPER Z30/44	B5010 DAMPERZ30/33/41/44	B5012 DAMPER Z38/40/44	B5014 DAMPER Z44/47	B5016 DAMPER Z38/44	B5017 DAMPER Z38/44	B5018 DAMPER Z44/47	B5020 DAMPER Z44/47	B5021 DAMPER Z40/44	B5022 DAMPER Z40/44	B5025 DAMPER Z41/42/44	B5027 DAMPER Z45/46	B5029 DAMPER Z36/45	B5030 DAMPER Z45/46	B5031 DAMPER Z36/45
4	Rev 62 - August 2023
5	Detection Zone	Zone Levels			VARIOUS	GROUND	GROUND	GROUND																						1st	1st	1st	1st	1st								PLANTROOM	GROUND	PLANTROOM	OUTSIDE
6
7	1	Ground Floor	Farnham Park doctors		●	○	○	○																						○		○	○	○								○	○	●	○
8	2	Ground Floor	Staircase between Farnham Park Doctors & Nurses		●	○	○	○																						○		○	○	○								○	○	●	○
9	3	Ground Floor	Staircase between Farnham Park Nurses & FICS		●	○	○	○																						○		○	○	○								○	○	●	○
10	4	Unknown
11	5	Ground Floor	Atrium		●	○	○	○																						○		○	○	○								○	○	●	○
12	6	Ground Floor	Outpatients Staircase			●	○	○																						○		○	○	○								●	○	○	○
13	7	All floors	Outpatients Lift				●																																			●		○
14	8	Ground Floor	Outpatients			○	○	●																						○		○	○	○								●	○	○	○
15	9	Ground Floor	Diagnostic & Treatment			○	○	○																						○		○	○	○								●	○	○	○
16	10	Ground Floor	Pharmacy		●	○	○	○																						○		○	○	○								●	○	●	○
17	11	Ground Floor	Café		○	○	○	○																						○		○	○	○								●	○	○	○
18	12	All floors	Main Lift																																							●		○
19	13	Ground Floor	Main Corridor		○	○	○	○																						○		○	○	○								●	○	○	○
20	14	Ground Floor	Staff Entrance		○	○	○	○																						○		○	○	○								●	○	○	○
21	15	All floors	Rear Lift																																							●		○
22	16	Ground Floor	Rear Staircase		○	○	○	○																						○		○	○	○								●	○	○	○
23	17	Ground Floor	Porters Storeroom		○	○	○	○																						○		○	○	○								●	○	○	○
24	18	Ground Floor	Main Kitchen		○	○	○	○																						○		○	○	○								●	○	○	○
25	19	Ground Floor	Hale Ward		○	○	○	○																						○		○	○	○								●	○	○	○
26	20	All floors	Inpatients Lift																																							●		○
27	21	Ground Floor	Inpatients Staircase		○	○	○	○																						○		○	○	○								●	○	○	○
28	22	Ground Floor	Inpatients		○	○	○	○																						○		○	○	○								●	○	○	○
29	23	Ground Floor	Bourne Ward		○	○	○	○																						○		○	○	○								●	○	○	○
30	24	Ground Floor	X-Ray Department		○	○	○	○																						○		○	○	○								●	○	○	○
31	25	Ground Floor	Main Staircase		○	○	○	○																						○		○	○	○								●	○	○	○
32	26	Ground Floor	Wheelchair Services		○	○	○	○																						○		○	○	○								●	○	○	○
33	27	Ground Floor	Physiotherapy Department		○	○	○	○																						○		○	○	○								●	○	○	○
34	28	First Floor	Frimley Health Physiotherapy		○	○	○	○																						○		○	○	○								●	○	○	○
C&E Matrix

 

[cannotunderstandexcel]

Sample Data.xlsx
	A	B	C	D																																											AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK
3																																															B4088 DAMPER ZONE 39	B5002 DAMPER Z44	B5004 DAMPER Z44	B5006 DAMPER Z38/44	B5008 DAMPER Z30/44	B5010 DAMPERZ30/33/41/44	B5012 DAMPER Z38/40/44	B5014 DAMPER Z44/47	B5016 DAMPER Z38/44	B5017 DAMPER Z38/44	B5018 DAMPER Z44/47	B5020 DAMPER Z44/47	B5021 DAMPER Z40/44	B5022 DAMPER Z40/44	B5025 DAMPER Z41/42/44	B5027 DAMPER Z45/46	B5029 DAMPER Z36/45
4	Detection Zone
5
6	1	Ground Floor	Farnham Park doctors
7	2	Ground Floor	Staircase between Farnham Park Doctors & Nurses
8	3	Ground Floor	Staircase between Farnham Park Nurses & FICS
9	4	Unknown
10	5	Ground Floor	Atrium
11	6	Ground Floor	Outpatients Staircase
12	7	All floors	Outpatients Lift
13	8	Ground Floor	Outpatients
14	9	Ground Floor	Diagnostic & Treatment
15	10	Ground Floor	Pharmacy
16	11	Ground Floor	Café
17	12	All floors	Main Lift
18	13	Ground Floor	Main Corridor
19	14	Ground Floor	Staff Entrance
20	15	All floors	Rear Lift
21	16	Ground Floor	Rear Staircase
22	17	Ground Floor	Porters Storeroom
23	18	Ground Floor	Main Kitchen
24	19	Ground Floor	Hale Ward
25	20	All floors	Inpatients Lift
26	21	Ground Floor	Inpatients Staircase
27	22	Ground Floor	Inpatients
28	23	Ground Floor	Bourne Ward
29	24	Ground Floor	X-Ray Department
30	25	Ground Floor	Main Staircase
31	26	Ground Floor	Wheelchair Services
32	27	Ground Floor	Physiotherapy Department
C&E Matrix

 

[cannotunderstandexcel]

Did you look at the 'Known XL2BB Issues' right near the top of the article?

Thanks Peter, I have followed those steps to get the mini sheet button working

 

[Peter_SSs]

Glad you got XL2BB going.

So why do we have slightly different XL2BB sample data in post #5 and post #6?

Also, how does this relate to the description in post #1? I don't see the connection.

 

[cannotunderstandexcel]

so sorry, as you can tell, I am not a whizz at this... 5 was my first attempt, then I realised it looked huge so 6 was me hiding more columns and trying again

I have made a simplified version which I will post below, if I had a sheet with the blue data listing all of the points around the site, could I make Excel read that data and create the green data automatically?

Sample Data.xlsx
	A	B	C	D	E	F	G	H	I
1	Point IDs	Triggered Zones				Point IDs
2	A101	1, 2			Alarm Zone	A101	A102	A103	A104
3	A102	2, 3			1	ü
4	A103	3, 4, 5			2	ü	ü		ü
5	A104	2			3		ü	ü
6	A105				4			ü
7	A106				5			ü
8	A107
9	A108
10	A109
11	A110
12	A111
13	A112
14	A113
15	A114
16	A115
17	A116
18	A117
Sheet1

 

[Peter_SSs]

Thanks for the simplified & smaller sample data and expected results. A suggestion for that, compatible with Excel 2016 as shown in your profile, is shown below. Are you still using that version as other solutions may be possible with later versions?

I have some doubts about relating that to your actual data though. In your post #6 sample data the headers in AU3:BK3 do not seem to appear anywhere in columns A:C. That is quite different to your simplified sample where the headers in F2:I2 do appear in column A.

Anyway, see if this is headed in the right direction.

23 08 17.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Point IDs	Triggered Zones				Point IDs
2	A101	1, 2			Alarm Zone	A101	A102	A103	A104	A105
3	A102	2, 3			1	ü
4	A103	3, 4, 5			2	ü	ü		ü
5	A104	2			3		ü	ü
6	A105				4			ü
7	A106				5			ü
8	A107				6
9	A108
10	A109
11	A110
12	A111
13	A112
14	A113
15	A114
16	A115
17	A116
18	A117
Alarms

Cell Formulas
Range	Formula
F3:J8	F3	=IF(ISNUMBER(SEARCH(" "&$E3&","," "&VLOOKUP(F$2,$A$2:$B$18,2,0)&",")),"ü","")