Any EXCEL whizz-kids out there....?

[Danorak81]

Firstly-don't hate: this on my work machine, which is some Windows thing :lol:

I have quite a large, complex EXCEL database that I run, and I'm kinda' self taught on many aspects of Conditional Formatting, PIVOT tables and certain conditions to change 'stuff' in cells etc: I am by no means mega competent, but I've learnt through perseverance. Most of the formatting is to minimise the time I spend inputting data when certain information is entered.

Anyway, I am trying to make a cell change colour if a certain text is entered in a different cell. I'll bullet point for clarity.

- Column L has names in it (the length of the column usually hits around the 3000 entries mark). The cells in the column all have a drop down menu with names in.
- All the way across in column AP is a simple "Y" or "N" text entry-no drop down in these cells.
- I want any cell in column L to change colour when "Y" is input to column AP on the same row.

I thought this would be quite easy, but it seems it is not. The 'IF' formula is one that has been suggested online by others looking to achieve similar, but I struggle to grasp how IF works fully (I have one example of this working on the database, but it is for something different), and I haven't been able to get it to work the way I need it to.

The other option was: Conditional Formatting > New Rule > Use a Formula to determine which cells to format.

This option has yielded the closest result, but what it has done is change the colour of every cell (admittedly, to the colour format I want) in column L regardless of the "Y" or "N" entry in the AP column. I selected all the cells in column L, and the formula that I have used is this: =$AP$2:$AP$3909="y". Despite the formula being set to "y", all cells in column L change colour, whether the column has Y, N, or is even blank.

For anybody that is reading, I hope I've explained that clearly! To those that are reading/understand, any idea why every cell is being formatted regardless of Y or N in column AP? Have I missed something basic?

TIA,
Dan

 

[Nermal]

I just had a little play, and if I'm understanding you correctly then the formula you want (in cell L2) is:

=$AP2="Y"

That should make L2 change colour when AP2 is "Y". You can then fill down and apply the formatting to the rest of the rows.

 

[Danorak81]

That, has solved it.

I spent a good 2 hours yesterday playing with rules and formulas. Looking through my scribbles, I was so close early on! You're a star-thank you so much! Now I can play catch up from yesterday :lol:

 

[maflynn]

Anyway, I am trying to make a cell change colour if a certain text is entered in a different cell. I'll bullet point for clarity.

I know you got your answer, but in all honesty, isn't this sort of question chatgpt would be good at answering? Something to consider maybe. I use chatgpt for similar type of queries, regrading excel, powershell, and python when I'm stuck

 

[Danorak81]

I know you got your answer, but in all honesty, isn't this sort of question chatgpt would be good at answering? Something to consider maybe. I use chatgpt for similar type of queries, regrading excel, powershell, and python when I'm stuck

Yes, it is: and I did turn to chatGPT-but it couldn't give me a definitive answer, and when I asked it to dive deeper, it directed me back to the first search result I used which didn't work.

 

[maflynn]

but it couldn't give me a definitive answer,

Ahh, yeah that's occurred to me on certain python scripts. I actually tried grok (dumb as rocks), and copilot, a little better with programming questions but still didn't give me the solution I needed.

 

[Danorak81]

Ahh, yeah that's occurred to me on certain python scripts. I actually tried grok (dumb as rocks), and copilot, a little better with programming questions but still didn't give me the solution I needed.

I really reluctantly used it too-I prefer, where possible, to try and work things out myself. I don't know if its an age thing, I just don't want to rely on stuff like that yet. It was through gritted teeth I typed it into copilot 😂

 

[maflynn]

I really reluctantly used it too-I prefer, where possible, to try and work things out myself.

i get it, but funnily enough as a programmer, that's how I learn and it seems much of the industry is content in steeling other people's code . If I'm unable to do something in powershell, I'll ask chatgpt, and it will give me just enough, and I take it run with it and extend it. I also figure out why it works, and its now part of my own tool chest of knowledge.

For excel, its more out out of laziness, most of the time, I know how to do it, but the details are fuzzy, I used to work with incredibly complex spreadsheets, with VBA macros and everything, but its been years and so some of the things I knew off the top of my head are a bit sketchy so chatgpt fills in the blanks

 

[Danorak81]

Your level of coding is well above my standard. I'm a graphic designer by trade, but I work in education, so EXCEL was kinda' forced onto me.

TBH, I used to have a Raspberry Pi4b that I set up and learnt how to code (gave up when it came to Amiga emulation though), but my main job is in education-which swallows a lot of time in the evening-but then add 2 young children into the mix-I just don't have the time/energy to delve into such things as I used to. My EXCEL stuff at work is quite time limited, hence the multitude of conditions and formulas to speed up my work flow. I like to work things out if I can, but should probably be more onboard with ChatGPT and Ai related resources, to 1) help me out and 2) make sure I understand enough when my children are a bit older and using technology...

 

[maflynn]

but I work in education, so EXCEL was kinda' forced onto me.

I used to be a power user of Excel, and at one point back in the day wrote a fund accounting application, with VBA, full suite of reports, including P&L, Balance sheet, etc. That was my prior life, now, I don't use excel much so when I need to do something, its no longer second nature. Plus MS introduced new formulas and functions, so chatgpt will get me up to speed when the need arises.

 

[sunapple]

Yes, it is: and I did turn to chatGPT-but it couldn't give me a definitive answer, and when I asked it to dive deeper, it directed me back to the first search result I used which didn't work.

I recommend asking for an example excel file, works well for me in Copilot. Especially with conditional formatting. Might give a different solution entirely.