Google sheets conditional formatting

[jogold]

A non-profit that I help out asked me to play with a sheet that was generated from a questionaire Google form. (Non-profit for me too, but I like what they do.)
It has about 100 collums and they would like me to make the first 5 collums show the results. So I need to figure out how to do the AND, OR and IF from many cells. Even better if I could use the Vlookup to populate with words instead of colors.
Can someone please point out a good place that I can learn from. Please, a resource that you've used and recommend, Google gives me enough untried places.
Thank you

 

[britechguy]

I don't know with enough precision what it is you're trying to do, but my advice is just to look up the EXCEL functions that get you the results you want. Google Sheets is, in essence, Google Excel, and the formula syntax is virtually identical. Hence the reason you can do virtually direct conversions from a Google Sheet into XLSX format and vice versa.

 

[jogold]

I know that it's basically pretty straightforward, until it's not.
The same AND formula works in column C refering to column Q AND R OR S AND T but when I do it to column AX and so on it stops working. Why
I also tried some more complicated ones and they don't work but there's no explanation of why.
So I figured that if I find a good place they will have a guru that will tell me why.

 

[britechguy]

Have you tried saving that Google Sheet as an XLSX file and seeing if the same formulae work in Excel itself? I realize that's not your end goal, but I don't think you're going to find some guru who will tell you why, to be perfectly honest. I've been down the road you're travelling before, and I generally have to figure out if it works in Excel (and sometimes, it hasn't, and it's been easier to figure out what will in Excel, then transferring the whole thing back to Google Sheets lock, stock, and barrel).

 

[jogold]

So far I'm figuring it out, slowly.
Excel is more powerful and I'm afraid I'll complicate it even more. Google is more idiot proof, and it allows you to access the sheet while playing around. Something sorely missing in Excel.

 

[Your PCMD]

So far I'm figuring it out, slowly.
Excel is more powerful and I'm afraid I'll complicate it even more. Google is more idiot proof, and it allows you to access the sheet while playing around. Something sorely missing in Excel.

@callthatgirl

 

[HCHTech]

When I'm working on a multi-element formula, I find it easier to break it into steps -- each in different cells. This confirms that each part is working correctly. Then, if necessary, you can combine everything at the end once you get the pieces right. Put your "Q or R" test formula in one cell, and your "S or T" test in another cell, etc.

 

[jogold]

Thanks, Yep, that's what I am doing and that's why I wrote "Slowly". I'm looking for shortcuts.

 

[mmerry]

Try this

 

[jogold]

He makes it look so simple.
There's a reason I became a tech and not a programmer.

I figured out the Conditional Formatting and I guess this will be the next stop.
Then I want to figure out how to have text appear in one cell depending on the results of other multiple cells. Sort of like the Conditional Formatting but with words instead of colors.

 

[AlohaTech]

You might be able to find some information here: https://www.benlcollins.com/

 

[jogold]

Thanks.

 

[britechguy]

Then I want to figure out how to have text appear in one cell depending on the results of other multiple cells. Sort of like the Conditional Formatting but with words instead of colors.

Look at the IF function. The logical condition can be as complex as you need it to be, and then you have what you want to show if true, followed by what to show if false. Just used it yesterday to suppress display of something in a cell unless another had been filled in first:

=IF(ISBLANK(B2), "", YEAR(B2)&"-"&TEXT(ROW()-1,"00"))

 

[jogold]

I wont claim to understand what you wrote but now I know which function to look into.
Thank you.

 

[britechguy]

I wont claim to understand what you wrote

Nor did I, until I reviewed the syntax. But, in English, that particular IF translates to: If the cell B2 is blank, then make the cell with this IF statement blank, otherwise make it equal to the year number from the date in B2 followed by a hyphen, followed by a 2 digit sequence number derived from the current ROW I'm in minus 1.

Excel (and, correspondingly, Google Sheets or any equivalent spreadsheet program) is just insanely complex. For other than "the stuff I need to use all the time" like basic sums and other math, I have to look up this stuff based on what I want. And I'm finding, more and more, that kinda-sorta natural language web searches are getting me straight to the correct resources for what it is I'm trying to do. I may have to review several different techniques based on my own circumstances, but almost always one of them is close enough to modify to my own purposes.

 

[jogold]

You're right, if I can get over my fear of programming (hated batchfiles back in the day) and just read the formulas they are pretty simple (ok, not so simple) and actually make sense.
This is going to be fun. I actually do enjoy logic, that's why I am good at troubleshooting. I just hate following other people rules.
Yeah, I wasn't a star student either.

 

[britechguy]

Remember, Excel started out life (and still continues a huge part of its existence) in the world of "bean counters," not programmers. It was supposed to make things easy for those who work with numbers, but who are not programmers, and who have familiarity with a great deal of accounting jargon.

I have found diving into Excel when I need to something I generally enjoy. It's amazing what it can do, and often do far more simply than one would ever imagine. This can be particularly true for a multi-sheet workbook where the "front sheet" that everyone sees does nothing more than massage data from one or more sheets behind it that no one generally does.

 

[jogold]

I finally figured out all the crazy different scenarios and conditional formatting, and it all works.
But for the life of me I can't figure out how to copy these from my test to the real one.
The web is full of copy and paste format and so on but it doesn't work.
What now?

 

[NETWizz]

I like XLOOKUP the best but for all I know that might be Excel only

 

[jogold]

Anyone on the copy to another sheet?