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

 

[Rofsal]

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

From row 13 to 29, it will change depending on the concepts, probably in other project there will be a pool and another row will have to be inserted
But the colum D will give me the final values, some times the references will be right on the D column, and some other times the D column will be the sum from column H and K that will be referenced from sheet2

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.

I'm looking to highlight all references in sheet1 from Sheet2, like:

=Sheet2!G224*0.8

=Sheet2!G279

=Sheet2!I285

=Sheet2!G384*0.9

and all those cells in Sheet2 should be highlighted

Some times I work with 5 to 7 proposals and I if leave one without reference it could be a very expensive mistake

I've tried conditional formats and some VBA codes but I haven't succed

thank you for the help!

 

[Eric W]

So you're looking for the cell reference, not the cell value? That's a bit trickier, but you can try:

Book3
	B	C	D	E	F	G	H	I	J	K
276		HOTEL								0
277	9.34	VENTANA DE ALUMINIO V-01 AK-0500(1.50X1.50M) 1 FIJO	PZA	88	3297.89	290214	7043.54	619832	4665.63	410575
278	9.35	VENTANA DE ALUMINIO V-02 AK-0500(1.50X1.50M) 1 FIJO	PZA	14	2198.59	30780.3	5722.85	80119.9	4665.63	65318.8
279						320994		699951		475894
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			0		0		0
285						641989		1399903		951789
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	9495	28485	16835	50505	36354.5	109063
291	10.13	PUERTA P-02 07001 PUERTA TIPO TAMBOR ABATIBLE (1.00X2.4M)	PZA	6	9495	56970	18500	111000	36196.9	217182
292	10.14	PUERTA P-02A 07001 PUERTA TIPO TAMBOR ABATIBLE (1.00X2.4M)	PZA	1	14073	14073	39000	39000	52495	52495
293	10.15	PUERTA P-03 07001 PUERTA TIPO TAMBOR ABATIBLE (0.78X2.4M)	PZA	1	9495	9495	16900	16900	34962.3	34962.3
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	66259	66259	109323	109323	236061	236061
295	10.17	LAMBRIN CON UN DESARROLLO DE 5.38M 3.63M DE ALTURA INCLUYE INSERTO PARA PUERTA	PZA	1	53063	53063	97546	97546	62394.8	62394.8
296	10.18	LAMBRIN CON UN DESARROLLO DE 5.17M 3.00M DE ALTURA INCLUYE INSERTO PARA PUERTA	PZA	1	42141	42141	53450	53450	49845.3	49845.3
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	40184	40184	49933	49933	46065.5	46065.5
298	10.2	PUERTA P-04 07001 PUERTA TIPO TAMBOR ABATIBLE (0.90X2.4M)	PZA	96	9495	911520	17300	1660800	36125.3	3468033
299	10.21	PUERTA P-05 07001 PUERTA TIPO TAMBOR ABATIBLE (0.80X2.4M)	PZA	84	9495	797580	16900	1419600	35662.3	2995631
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	1100	92400	1650	138600	7173.22	602551
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	5064	425376	5950	499800	13069.5	1097840
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	10200	856800	8300	697200	18028	1514351
303	10.25	MURO A BASE DE DUELA DE TZALAM DE 10CMX90CMX19 MM DE ESPESOR, EN TONALIDAD CAFÉ, VETADO SUAVE, ACABADO NATURAL,	M2	29.4	2754	80967.6	3756	110426	13135.3	386179
304		CENTRO COMERCIAL
305	10.18					0		0		0
306						3475314		6453986		1.2E+07
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)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B276:K306	Expression	=SUMPRODUCT(--ISNUMBER(SEARCH(SUBSTITUTE(CELL("address",B276),"$",""),FORMULATEXT(Sheet1!$D$13:$K$40))))	text	NO

I manually changed one of the values on Sheet1 to reference F278, and you can see it's shaded green. As always with CF, I selected the range B276:K306, and the B276 in the formula refers to that upper left corner. The Sheet1!$D$13:$K$40 range in the formula refers to the range on Sheet1 where we look for the reference.

But there are several issues with this formula. It only finds references like =Sheet2!F278*.05 , but it won't find =Sheet2!$F$278*.05. No dollar signs. Also, the CELL function can be a bit dicey. Finally, it would also highlight F278 if it saw this formula: =F278*.05, without the sheet name. These can be addressed if you want, but you might be better off with a macro.

 

[Rofsal]

So you're looking for the cell reference, not the cell value? That's a bit trickier, but you can try:

Yes!!! that's exactly why it's being so complicated, because I havent found a formula in excel that address the reference

But there are several issues with this formula. It only finds references like =Sheet2!F278*.05 , but it won't find =Sheet2!$F$278*.05. No dollar signs.

And many times I have to lock the cells

These can be addressed if you want, but you might be better off with a macro.

I'm totally open, the problem is that I'm not so good with VBA, I'll apreciate all the help

Again Thank You so much for your time and patience

 

[Eric W]

I haven't heard if you've tried the CF rule from post 12 yet, or how it's worked. As an alternative, here's a simple macro that might help.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code into the window that opens:

Sub FindDependents()
Dim c As Range

    ActiveSheet.ClearArrows
    For Each c In ActiveSheet.UsedRange
        c.ShowDependents
    Next c
    
End Sub

Press Alt-Q to close the editor. Go to your Sheet2. Press Alt-F8 to open the macro selector. Choose FindDependents and click Run. Instead of highlighting, you'll see arrows coming from every cell that is used in a formula. Cells referenced on a different sheet point to a little picture of a sheet. So if you have a cell you think should be referenced, and it doesn't have an arrow, you know you need to check it. You can do this manually, just by selecting each cell, and from the Formula tab click the Trace Dependents button. It just depends on how many cells you need to do it to.

 

[Rofsal]

Hi Eric!!

Thank you for following up!!!
I've tried the CF but didn't have any luck with mya actual file, let try this VBA and I'll get back to you, I hope tomorrow, today is a busy day!

Saludos!

Rodolfo

 

[Rofsal]

Hi Eric!

I tried the macro and it made my excel crahs, at first it was just show the arrows with dependences but then it crashed