Forum Widgets
Latest Discussions
Conditional formatting for a whole column, based on same-row-cells, but in one single rule
I need conditional formatting, where the color of the cell in column F is changed based on the value in the cell in column W in the same row: I have done it like this: The cell F119 changes colour to orange if this is true: =OG($W$119<>"x";$W$119<>"CNG") This works fine. But I have not been able to find a way to make one rule do this for the entire column. So I have set an individual rule for each cell in column F and I have 376 rows so far. When I need to change the rule, or troubleshoot the rule, this is not operable. Also a lot of the cells have been edited with copy-paste, and now the conditional formatting is all over the place 😒 I would prefer one single rule, that checks the cell in column W in each row, and changes the colour in cell F in the same row, if the conditions are true.
combine multiple text into single line of text
Hi is there a way to combine all the cells into a single cell (of text) ignoring the duplicates/repeat values. i know concatenate but that function becomes very lengthy as ill have to define every cell into the function individually. is there any other function where i simply select the entire data (like an array) and it can do the required.
Complex formulae with data model
Over the years, I have developed a significant excel data sheet which has gotten slower and slower with time. I have discovered the idea of excel's data model this week and have learnt how to import the data I need from its source CSV to the data model and I now have a power pivot which looks like what I originally had in excel. What I would like to do next, is to replicate some of the formulae I had in excel to calculate if something should be counted or not. I would not want the red one to be counted as this row would have been counted in the previous month. In excel I was using the following formula- =IF(COUNTIF(B5:M4,">0"),SUM(Patient!B5:M5*0),Patient!N5) is there a way of achieving this with the data model or would I have to hardcode this into another sheet? I was trying to avoid doing this to minimise the number of formulae... Many thanks for your help...
