Highlight referenced cell form other sheets in the same workbook

[Rofsal]

Hi all!

I need to highlight a cell form sheet 2 that is referenced in sheet 1 (See images attached)

What I have in Sheet1 is information referenced to sheet2:

And in Sheet2 I have some information

What I'm looking for is a formula that highlights in shet2 all the cells that are being referenced to sheet1 like this:

I apreciate your time and help!

Rof

 

[Eric W]

On Sheet2, select the C4:I12 range. Click Conditional Formatting > New Rule > Use a formula > and enter:

=COUNTIF(Sheet1!$C$4:$C$11,C4)

click Format. . . and select your fill color.

 

[Rofsal]

On Sheet2, select the C4:I12 range. Click Conditional Formatting > New Rule > Use a formula > and enter:

=COUNTIF(Sheet1!$C$4:$C$11,C4)

click Format. . . and select your fill color.

Thank you Eric, I've tried that but doesn't work, as I'm looking for I get this:

its Highlighting the cell next to it

 

[Rofsal]

On Sheet2, select the C4:I12 range. Click Conditional Formatting > New Rule > Use a formula > and enter:

=COUNTIF(Sheet1!$C$4:$C$11,C4)

click Format. . . and select your fill color.

Thank you!! I've solved it in the sample I've attached.... but it goes different when I'm trying to apply to my current workbook (i've formated the cells to be highlighted purple) ...see images attached:

 

[Eric W]

I'm a bit confused as to what you want highlighted. You want S245 or V254 to be highlighted (they're both purple)? The biggest issue with Conditional Formatting is making sure you have the ranges right. If you select a multi-cell range, the formula should reference the upper left corner, and the usual relative/absolute reference rules apply as the formula is applied to the other cells in the range.

You've also come across the issue that if you simplify your question too much, you'll get an answer that doesn't work on your original problem. Also, consider using the XL2BB tool (see my signature, or the XL2BB button in the reply box). That makes it much easier to work with your sample sheets than trying to manually recreate your samples from a picture.

 

[Rofsal]

I'm a bit confused as to what you want highlighted. You want S245 or V254 to be highlighted (they're both purple)? The biggest issue with Conditional Formatting is making sure you have the ranges right. If you select a multi-cell range, the formula should reference the upper left corner, and the usual relative/absolute reference rules apply as the formula is applied to the other cells in the range.

You've also come across the issue that if you simplify your question too much, you'll get an answer that doesn't work on your original problem. Also, consider using the XL2BB tool (see my signature, or the XL2BB button in the reply box). That makes it much easier to work with your sample sheets than trying to manually recreate your samples from a picture.

Yes, I tried to make it simple but didn`t work I've downloaded the XL2BB lets see if I make this right

I'm pasting the original file I'm working with

Presupuesto base R1 prueba celdas referenciadas.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
4
5	Fecha	03/08/2020
6	Rev.	18/03/2020		SUP. TERRENO	974.43	100.00%		% Contra total	% Contra torre		% Contra total	% Contra sótanos
7	Diseño:	09/03/2020		SUP. TERRENO USO	411.05	42.18%		34.93%	53.67%		65.07%	186.31%
8
9	ID	PARTIDA		TOTAL ÁREAS				SOTANOS			TORRE
10				20,384.72				7,119.69			13,265.03
11				TOTAL	$ / M2	%		IMPORTE	$ / M2		IMPORTE	$ / M2
12
13	1	CONDICIONES GENERALES		$ 6,853,410.08	$ 336.20	2.91%		$ 2,393,663.25	$ 336.20		$ 4,459,746.82	$ 336.20
14	2	PRELIMINARES		$ 16,652,803.29	$ 816.93	7.07%		$ 5,816,258.31	$ 816.93		$ 10,836,544.98	$ 816.93
15	3	CIMENTACION		$ 10,198,715.39	$ 500.31	4.33%		$ 3,562,064.72	$ 500.31		$ 6,636,650.67	$ 500.31
16	4	SUB ESTRUCTURA Y SUPER ESTRUCTURA		$ 70,198,120.06	$ 3,443.66	29.79%		$ 24,517,817.92	$ 3,443.66		$ 45,680,302.13	$ 3,443.66
17	5	ALBAÑILERIA		$ 15,300,663.25	$ 750.59	6.49%		$ 5,344,001.74	$ 750.59		$ 9,956,661.51	$ 750.59
18	6	ELEVADORES		$ 6,090,096.50	$ 298.76	2.58%		$ 2,127,063.76	$ 298.76		$ 3,963,032.74	$ 298.76
19	7	ACABADOS		$ 14,038,050.50	$ 688.66	5.96%		$ 2,807,610.10	$ 2,962.49		$ 11,230,440.40	$ 2,962.49
20	8	FACHADA		$ 2,543,019.27	$ 124.75	1.08%		$ -	$ -		$ -	$ -
21	9	CANCELERÍA		$ 2,543,019.27	$ 124.75	1.08%		$ -	$ -		$ 2,543,019.27	$ 1,270.02
22	10	CARPINTERÍA		$ 6,234,007.22	$ 305.82	2.65%		$ -	$ -		$ 6,234,007.22	$ 2,123.63
23	11	HERRERÍA		$ 4,773,059.47	$ 234.15	2.03%		$ 477,305.95	$ 36.43		$ 4,295,753.52	$ 36.43
24	12	IMPERMEABILIZACIÓN		$ 1,784,259.85	$ 87.53	0.76%		$ 623,181.34	$ 87.53		$ 1,161,078.52	$ 87.53
25	13	INSTALACIONES		$ 67,923,582.61	$ 3,332.08	28.82%		$ 40,488,088.73	$ 2,068.26		$ 27,435,493.88	$ 2,068.26
26	14	ÁREAS EXTERIORES		$ 861,410.32	$ 42.26	0.37%		$ 350,000.00	$ -		$ 511,410.32	$ 38.55
27	15	Imprevistos (3.5%)		$ 9,656,471.59	$ 473.71	4.10%		$ 2,816,125.53	$ 395.54		$ 6,840,346.06	$ 515.67
28	16	Fit Out (5%)		$ -	$ -	0.00%		$ -	$ -		$ -
29	17	Tenant Improvementes y OC´s		$ -	$ -	0.00%		$ -	$ -		$ -	$ -
30
31		IMPORTE TOTAL S/IVA		$ 235,650,688.64	$ 11,560.16	100.00%		$ 91,323,181.35	$ 12,826.85		$ 141,784,488.03	$ 10,688.59
32		IVA		$ 37,704,110.18	$ 1,849.63			$ 14,611,709.02	$ 2,052.30		$ 22,685,518.08	$ 1,710.17
33		IMPORTE TOTAL C/IVA		$ 273,354,798.83	$ 13,409.79			$ 105,934,890.36	$ 14,879.14		$ 164,470,006.11	$ 12,398.77
34
35	ANÁLISIS DE NEGOCIO			Área vendible	10,503.87						Vendible / Construido	0.52
36	$/m2 área vendible			$ 235,650,688.64	$ 22,434.66
Sheet1

Cell Formulas
Range	Formula
B5	B5	=TODAY()
F7	F7	=SUM(E7/E6)
H7,K7	H7	=+H10/$D$10
I7	I7	=H10/K10
L7	L7	=SUM(K10/H10)
D10,D22:D29,D13:D18	D10	=SUM(H10+K10)
H10	H10	=6788.88+330.81
E13:E29	E13	=SUM(D13/$D$10)
F13:F29	F13	=SUM(D13/$D$31)
D19,D21	D19	=H19+K19
D20	D20	=Sheet2!G279
I27,L27	I27	=SUM(I13+I14+I15+I16+I17+I18+I19+I20+I21+I22+I23+I24+I25+I26)*0.035
H27:H29,H24,H20:H22,H13:H18	H13	=SUM(I13*$H$10)
H19	H19	=Sheet2!G224*0.2
H23	H23	=Sheet2!G384*0.1
H25	H25	=Sheet2!I435+Sheet2!I442+Sheet2!G451++Sheet2!G461+Sheet2!G478
H26	H26	=Sheet2!G509
K24:K29,K20,K13:K18	K13	=SUM(L13*$K$10)
K19	K19	=Sheet2!G224*0.8
K21	K21	=Sheet2!G279
K22	K22	=Sheet2!I285
K23	K23	=Sheet2!G384*0.9
D31,K31,H31,F31	D31	=SUM(D13:D29)
E31:E33	E31	=+D31/D$10
I31,L31	I31	=+H31/H10
D32,K32:L32,H32:I32	H32	=+H31*0.16
D33,K33:L33,H33:I33	H33	=+H31+H32
L35	L35	=SUM(E35/D10)
D36	D36	=SUM(D31)
E36	E36	=SUM(D36/E35)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C13	Other Type	Icon set		NO

In the D, H and K colums after the total row I have referenced values from Sheet2

I need those referenced cells from Sheet 2 to be highlighted
(I'm pasting sheet 2)

Presupuesto base R1 prueba celdas referenciadas.xlsx
	B	C	D	E	F	G	H	I	J	K
276		HOTEL								$ -
277	9.34	VENTANA DE ALUMINIO V-01 AK-0500(1.50X1.50M) 1 FIJO	PZA	88.00	$ 3,297.89	$ 290,214.00	$ 7,043.54	$ 619,831.52	$ 4,665.63	$ 410,575.44
278	9.35	VENTANA DE ALUMINIO V-02 AK-0500(1.50X1.50M) 1 FIJO	PZA	14.00	$ 2,198.59	$ 30,780.27	$ 5,722.85	$ 80,119.90	$ 4,665.63	$ 65,318.82
279						$ 2,543,019.27		$ 3,117,003.61		$ 2,643,492.27
280
281	10.1	Carpintería			Empresa 1		Empresa 2		Empresa 3
282	#	CONCEPTO	UNIDAD	CANTIDAD	$ Unitario	Total	$ Unitario	Total	$ Unitario	Total
283		SOTANOS
284	10.11	Cocinas completas, incluye estufa, iluminación bajo alacena, lavabo, grifería, herrajes y jaladeras	Pza			$ -		$ -		$ -
285						$ 5,086,038.54		$ 6,234,007.22		$ 5,286,984.54
286
287	10.2	Carpintería			Empresa 1		Empresa 2		Empresa 3
288	#	CONCEPTO	UNIDAD	CANTIDAD	$ Unitario	Total	$ Unitario	Total	$ Unitario	Total
289		HOTEL
290	10.12	PUERTA P-01 07001 PUERTA TIPO TAMBOR ABATIBLE (1.20X2.4M)	PZA	3.00	$ 9,495.00	$ 28,485.00	$ 16,835.00	$ 50,505.00	$ 36,354.47	$ 109,063.42
291	10.13	PUERTA P-02 07001 PUERTA TIPO TAMBOR ABATIBLE (1.00X2.4M)	PZA	6.00	$ 9,495.00	$ 56,970.00	$ 18,500.00	$ 111,000.00	$ 36,196.93	$ 217,181.61
292	10.14	PUERTA P-02A 07001 PUERTA TIPO TAMBOR ABATIBLE (1.00X2.4M)	PZA	1.00	$ 14,073.00	$ 14,073.00	$ 39,000.00	$ 39,000.00	$ 52,494.99	$ 52,494.99
293	10.15	PUERTA P-03 07001 PUERTA TIPO TAMBOR ABATIBLE (0.78X2.4M)	PZA	1.00	$ 9,495.00	$ 9,495.00	$ 16,900.00	$ 16,900.00	$ 34,962.32	$ 34,962.32
294	10.16	LAMBRIN ML-01 07002 CON UN DESARROLLO DE 1.31+4.93+0.64+0.51M 3.30M DE ALTURA A BASE DE MURO LAMBRIN SOBRE BASTIDOR	PZA	1.00	$ 66,259.00	$ 66,259.00	$ 109,323.00	$ 109,323.00	$ 236,060.97	$ 236,060.97
295	10.17	LAMBRIN CON UN DESARROLLO DE 5.38M 3.63M DE ALTURA INCLUYE INSERTO PARA PUERTA	PZA	1.00	$ 53,063.00	$ 53,063.00	$ 97,546.00	$ 97,546.00	$ 62,394.82	$ 62,394.82
296	10.18	LAMBRIN CON UN DESARROLLO DE 5.17M 3.00M DE ALTURA INCLUYE INSERTO PARA PUERTA	PZA	1.00	$ 42,141.00	$ 42,141.00	$ 53,450.00	$ 53,450.00	$ 49,845.34	$ 49,845.34
297	10.19	LAMBRIN ML-04 07002 CON UN DESARROLLO DE 4.93M 3.00M DE ALTURA INCLUYE INSERTO PARA PUERTA A BASE DE MURO LAMBRIN	PZA	1.00	$ 40,184.00	$ 40,184.00	$ 49,933.00	$ 49,933.00	$ 46,065.47	$ 46,065.47
298	10.20	PUERTA P-04 07001 PUERTA TIPO TAMBOR ABATIBLE (0.90X2.4M)	PZA	96.00	$ 9,495.00	$ 911,520.00	$ 17,300.00	$ 1,660,800.00	$ 36,125.34	$ 3,468,032.67
299	10.21	PUERTA P-05 07001 PUERTA TIPO TAMBOR ABATIBLE (0.80X2.4M)	PZA	84.00	$ 9,495.00	$ 797,580.00	$ 16,900.00	$ 1,419,600.00	$ 35,662.27	$ 2,995,631.03
300	10.22	MUEBLE M-01 07003 0.52X0.90X0.20M CUBIERTA PARA LAVABO A BASE DE ESTRUCTURA DE PTR DE 1 X 2''	PZA	84.00	$ 1,100.00	$ 92,400.00	$ 1,650.00	$ 138,600.00	$ 7,173.22	$ 602,550.65
301	10.23	MUEBLE M-02 07003 1.85X0.54X0.85 ESCRITORIO SEGÚN DISEÑO A BASE DE BASTIDOR DE MADERA DE 1" CUBIERTO POR AMBAS CARAS CON HOJA DE MDF DE 6 MM CON CHAPA DE MADERA NATURAL NOGAL	PZA	84.00	$ 5,064.00	$ 425,376.00	$ 5,950.00	$ 499,800.00	$ 13,069.52	$ 1,097,840.04
302	10.24	MUEBLE M-03 07003 CLOSET 0.90X0.60X2.79M FABRICADO CON REPISA Y BASE CON BASTIDOR DE MADERA, FORRADO POR AMBAS CARAS CON UNA HOJA DE MDF CON CHAPA MADERA NATURAL NOGAL	PZA	84.00	$ 10,200.00	$ 856,800.00	$ 8,300.00	$ 697,200.00	$ 18,027.99	$ 1,514,351.19
303	10.25	MURO A BASE DE DUELA DE TZALAM DE 10CMX90CMX19 MM DE ESPESOR, EN TONALIDAD CAFÉ, VETADO SUAVE, ACABADO NATURAL,	M2	29.40	$ 2,754.00	$ 80,967.60	$ 3,756.00	$ 110,426.40	$ 13,135.33	$ 386,178.78
304		CENTRO COMERCIAL
305	10.18					$ -		$ -		$ -
306						$ 3,475,313.60		$11,288,090.62		$ 16,159,637.84
Sheet2

Cell Formulas
Range	Formula
G277:G278	G277	=SUM(E277*F277)
G279,K279,I279	G279	=SUM(G242:G278)
K290:K303,I290:I303,G290:G303,K276:K278,I277:I278	I277	=SUM($E277*H277)
G284,K284,I284	G284	=SUM($E$284*F284)
G285,K285,I285	G285	=SUM(G237:G284)
G305,K305,I305	G305	=SUM($E$305*F305)
G306	G306	=SUM(G289:G305)
I306,K306	I306	=SUM(I283:I305)

 

[Rofsal]

I'm a bit confused as to what you want highlighted. You want S245 or V254 to be highlighted (they're both purple)? The biggest issue with Conditional Formatting is making sure you have the ranges right. If you select a multi-cell range, the formula should reference the upper left corner, and the usual relative/absolute reference rules apply as the formula is applied to the other cells in the range.

You've also come across the issue that if you simplify your question too much, you'll get an answer that doesn't work on your original problem. Also, consider using the XL2BB tool (see my signature, or the XL2BB button in the reply box). That makes it much easier to work with your sample sheets than trying to manually recreate your samples from a picture.

Hi Eric!
Did I complicated it to much?

 

[Eric W]

Sorry I haven't gotten back to you yet! Busy day at work. I'll come back to this as soon as I can.

 

[Eric W]

In the D, H and K colums after the total row I have referenced values from Sheet2

I need those referenced cells from Sheet 2 to be highlighted

Which row is that? 13 or 36 maybe? Will it change depending on where the word TOTAL happens to be?

Which values on Sheet2 do you want highlighted? Anything in the F:K columns that are found in Sheet1, row 13? In this example, I couldn't find any matches. If there just didn't happen to be any in this example, try to manually create one.

 

[Rofsal]

Sorry I haven't gotten back to you yet! Busy day at work. I'll come back to this as soon as I can.

I Know the feelling!!! I hope this will help me to focus on the most important stuff instead of being worried if I left a cost without reference

Thank you for your help and your patience