Sep 03 2019 02:04 AM
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
Sep 03 2019 02:44 AM
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
Sep 03 2019 02:55 AM
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
Sep 03 2019 03:19 AM
@IJSKO In that case you can use 'Trace Dependents' to find if any referral is made within this sheet/cell
Sep 03 2019 03:46 AM
@tauqeeracma But how do i make the color change when there is a referral?
Could you help me with that as well?
Sep 03 2019 04:20 AM
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
Sep 03 2019 04:28 AM - edited Sep 03 2019 04:30 AM
@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?
Sep 03 2019 05:30 AM
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
Sep 03 2019 06:50 AM
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.
Sep 03 2019 07:32 AM
please check the attachd documents
Sep 03 2019 10:21 AM
Wow, this looks like what i want!
That's great!
Do i need to use that extra collum? For the helper formula?
Sep 03 2019 10:44 AM
@IJSKO you may also use attached file.
Sep 03 2019 11:07 AM
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.
Sep 03 2019 11:39 AM
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
Sep 03 2019 11:54 AM - edited Sep 03 2019 12:00 PM
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.
Sep 03 2019 10:45 PM
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
Sep 03 2019 10:50 PM
@IJSKO Hello,
Wehn I change the range in "applies to" box, the formatting works for all the cells that is int he first sheet.
Sep 04 2019 12:18 AM - edited Sep 04 2019 12:22 AM
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
Sep 04 2019 12:59 AM
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.
Sep 04 2019 01:36 AM
I attached two files. Sheet2 with a range i cán change and sheet1 with a range i can't change