Conditional Formatting / Reference

[A.Fish]

I don't think this question is really specific to Conditional Formatting, but that is what I am attempting to do.

I have a spreadsheet that I've been working with over multiple years, and I have a new/separate sheet for each year. I've set it up so that each sheet compares information being input on the current sheet to all previous sheets, and then using conditional formatting, changes the color of the cell if duplicates are found using the following formula:

COUNTIFS($C$2:$C$5000;C145;$E$2:$E$5000;E145) + COUNTIFS('2019'.$C$2:$C$5000;C145;'2019'.$E$2:$E$5000;E145) + COUNTIFS('2018'.$C$2:$C$5000;C145;'2018'.$E$2:$E$5000;E145) + COUNTIFS('2017'.$C$2:$C$5000;C145;'2017'.$E$2:$E$5000;E145) + COUNTIFS('2016'.$C$2:$C$5000;C145;'2016'.$E$2:$E$5000;E145) + COUNTIFS('2015'.$C$2:$C$5000;C145;'2015'.$E$2:$E$5000;E145) + COUNTIFS('2014'.$C$2:$C$5000;C145;'2014'.$E$2:$E$5000;E145) > 1

This works fine, the problem is with each new year, I'm having to manually expand the code to reference the new/current year. It also only changes the color of the cell if it is a duplicate of the current year sheet or before, but does not change the color on the previous years sheet if it duplicates on a current/future years sheet. (ie. if there is a duplicate entry on sheet 2019 and sheet 2015, only the duplicate entry on 2019 will be highlighted, whereas the one on the 2015 sheet is not - unless I go back and manually modify the formula on each previous years sheet as well.

Ideally I was hoping to put one formula in place that would account for all past and future sheets, including sheets that have not yet been created (but would still be named with the year).

I thought that maybe I could accomplish this by using the INDIRECT function, but I am struggling with how to reference a cell on a separate sheet. I'm unsure if this would be a valid solution for what I am trying to accomplish or not, but would like to know how to reference a cell on a separate sheet using the INDIRECT function regardless.

Any help or suggestions would be greatly appreciated.

Thanks,
A.Fish

[MrProgrammer]

Hi, and welcome to the forum.

I don't think this question is really specific to Conditional Formatting, but that is what I am attempting to do. … Any help or suggestions would be greatly appreciated.

Put all of your data on a single sheet. Then the whole problem goes away. Use Data → Filter if you want to work with subsets of the data, say by year. Read section 7. Analyzing and modifying your data in Ten concepts that every Calc user should know. Analyzing data spread across sheets is difficult, at best.

Separating related data onto multiple sheets is a frequent cause of problems for people new to Calc. You will find hundreds of similar problems here for which the solution was to store all the data in one sheet.

COUNTIFS($C$2:$C$5000;C145;$E$2:$E$5000;E145) + COUNTIFS('2019'.$C$2:$C$5000;C145;'2019'.$E$2:$E$5000;E145) + COUNTIFS('2018'.$C$2:$C$5000;C145;'2018'.$E$2:$E$5000;E145) + COUNTIFS('2017'.$C$2:$C$5000;C145;'2017'.$E$2:$E$5000;E145) + COUNTIFS('2016'.$C$2:$C$5000;C145;'2016'.$E$2:$E$5000;E145) + COUNTIFS('2015'.$C$2:$C$5000;C145;'2015'.$E$2:$E$5000;E145) + COUNTIFS('2014'.$C$2:$C$5000;C145;'2014'.$E$2:$E$5000;E145) > 1

In the future, it is best to attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). A formula without the data is often of no help at all.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[A.Fish]

Thanks for the reply, and while your suggestion of moving everything to a single sheet would likely make solving this specific issue easier, unfortunately it would also create many additional problems due to the way the document is currently structured. I was hoping for a quick/easy fix that would allow me to transition to a new year without having to manually modify the formula each year. If that's not an option I'll just have to muddle along until I can find the time to restructure/build the document.

On the second part of my original question though, do you know how to reference a cell on a separate sheet using the INDIRECT function? I've looked but don't seem to be able to find the answer. (perhaps I'm just not using the correct key words when searching/googling) Or should I be posting that as a separate question?

Thanks again

Put all of your data on a single sheet. Then the whole problem goes away. Use Data → Fileter if you want to work with subsets of the data, say by year. Read section 7. Analyzing and modifying your data in Ten concepts that every Calc user should know. Analyzing data spread across sheets is difficult, at best.

[Villeroy]

This may be a time saver: Merge sheets of one spreadsheet document

[A.Fish]

This may be a time saver: Merge sheets of one spreadsheet document

I'm not real familiar with Macros but I'll take a look as it appears it would simplify the transfer of data.

Villeroy, there appears to be something not working quite right with the example you provided. When the sheets are merged, the numbers being brought to the first/target sheet do not match the numbers that are on the original sheet. Unless there was an intentional calculation of some sort in your example that I missed? << - This appears to be due to the RAND function used to populate the sheets executing again when the macro is run, thus changing the figures on the sheets, but the original figure is transferred to the Target sheet prior to changing.

I did notice though that the macro transfers the value/result of any formula in the cells as opposed to the actual formula located in the cells. Is there a way to carry over the actual cell formula as opposed to the result of that formula when merging?

Thanks

[Villeroy]

The macro transfers values on purpose. If you change getDataArray and setDataArray to getFormulaArray and setFormulaArray you get a transfer of formulas and values. And you may discover why I did not do that in the first place.

Quite often you don't need those formulas not any longer once the data are merged. Formulas that refer to each row can be copied and pasted along the merged column. formulas that refer to each column of a section (year in this case) become obsolete if you use pivot tables.