custom layout worksheet

Copper Contributor

I'm looking for a way to get something done

 

Say I have a cell in sheet1 and i'm giving it the value of a cell in sheet2 by putting a referral to it on sheet1.

Originally the fontcolor in sheet2 is blue, but i would like it to change to black if the cel has a referal on sheet1.

Is it possible to make a formula to get this done?

And is my question even clear?

 

Txs

Caroline

20 Replies

Hi @IJSKO 

 

You can use below formula in conditional formatting, hope it will work as required.

 

=MID(FORMULATEXT(E5),2,6)="Sheet1"

 

Sample file is also attached for reference.

Thanks,

Tauqeer

@tauqeeracma 

It's close but in your case the color changes in the the ceel whereto the referral goes, but i would like, in this cas, tot change the color in Sheet1!E4. So that the file sees that there is a referral to this cell

@IJSKO  In that case you can use 'Trace Dependents' to find if any referral is made within this sheet/cell

 

clipboard_image_0.png

@tauqeeracma But how do i make the color change when there is a referral?

Could you help me with that as well?

@IJSKO 

Hi

our Team mate @tauqeeracma  has guided you correctly to conditional formatting rule.

I would suggest @tauqeeracma  to add a second rule to achieve the desired outcome (same exact concept but, when condition when is not met, condition 2 will take over

Good Luck

Nabil Mourad

@IJSKO If the referenced value is unique you can use MATCH to check the value exists in the other sheet. Than use this formula in conditional formatting. Is it possible for you to share the workbook?

Thanks @nabilmourad .

 

I think the second rule will also not help because as per my understanding the requirement is to colour the cell if it is being called in another sheet. Whereas I put a conditional formatting rule in the cell that receives value from other sheet. 

 

Sample data file will surely help to suggest more specific solution.

Tauqeer

@erol sinan zorlu 

I cannot share the original workbook, but the example yes.

In sheet 1 i add my numbers and the moment i refer to these data in another sheet i would to have the color changed in the cel on sheet 1. 

please check the attachd documents

 

@erol sinan zorlu 

Wow, this looks like what i want!

That's great!

Do i need to use that extra collum? For the helper formula?

@IJSKO  you may also use attached file.

@IJSKO 

Hi,

I just hastly created a formula to solve your problem. However now I have a little bit more time and updated the formula. you can check the attached file.

@erol sinan zorlu 

Hi, great, but i cannot seem to adjust this one though. 

I did manage to change this one

=ALS.FOUT(VERGELIJKEN("=" &ADRES(RIJ(Sheet1!$E4);KOLOM(Sheet1!$E4);4;;"Sheet1");INDEX(FORMULETEKST(Sheet2!$E$5:$E$80);;);0);0)

 

But not this one

Sheet1!$E$4:$E$7 . i try to make this into E$80 as well, because now i can only make it happen to a few lines

@IJSKO 

Which version of Excel do you use?

 

If you want the formula to be applied a wider range then you need to change then you need to change the target range of the rule as seen in the attached picture.

@erol sinan zorlu 

 

The version is 16.26 (office365 package)

 

And i tried to change that range to a higher number, as i did in the formula, but it didn't save

@IJSKO Hello,

 

Wehn I change the range in "applies to" box, the formatting works for all the cells that is int he first sheet.

I try that, change the range to Sheet1!$E$4:$E$70 and give an oke.
But when i open the rules again it is again Sheet1!$E$4:$E$7

 

In your former file ConditionalFormatting2 i did manage to change that range in that field. 

So maybe make a hidden collum for that helper

@IJSKO 

I have tried several times to recreate the problem you have on the sample file and failed. Whenever I change the "applies to" range and close the dialog the new range is applied to the rule. There can be something different going on.

@erol sinan zorlu 

 

I attached two files. Sheet2 with a range i cán change and sheet1 with a range i can't change