Conditional Formatting / Reference

Discuss the spreadsheet application

Conditional Formatting / Reference

Postby A.Fish » Wed Feb 05, 2020 7:38 pm

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
Windows 7
OpenOffice 4.1.2
A.Fish
 
Posts: 3
Joined: Wed Aug 21, 2019 7:37 pm

Re: Conditional Formatting / Reference

Postby MrProgrammer » Wed Feb 05, 2020 8:45 pm

Hi, and welcome to the forum.

A.Fish wrote: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.

A.Fish wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3960
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Conditional Formatting / Reference

Postby A.Fish » Wed Feb 05, 2020 10:08 pm

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


MrProgrammer wrote: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.
Windows 7
OpenOffice 4.1.2
A.Fish
 
Posts: 3
Joined: Wed Aug 21, 2019 7:37 pm

Re: Conditional Formatting / Reference

Postby Villeroy » Wed Feb 05, 2020 10:23 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Conditional Formatting / Reference

Postby A.Fish » Wed Feb 05, 2020 10:42 pm

Villeroy wrote: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
Windows 7
OpenOffice 4.1.2
A.Fish
 
Posts: 3
Joined: Wed Aug 21, 2019 7:37 pm

Re: Conditional Formatting / Reference

Postby Villeroy » Thu Feb 06, 2020 12:36 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 28 guests