Скачать или смотреть Query | Custom Formula | Conditional Formatting | Indirect | Google Sheets

#googleforeducation #googleforedu #query #customformula #conditionalformatting #indirect #dropdown #gegagra #techiekrish

The Big Picture: Dynamic, Data-Driven Formatting

Imagine you want to highlight specific cells in your Google Sheet, but the criteria for highlighting should:

Change automatically based on data elsewhere.
Use complex logic beyond simple "greater than" or "less than" rules.
Reference data in other sheets or ranges, even if those sheet names or ranges change.
That's where these tools come in!

The Components:

Query:
Think of QUERY as a powerful data filter and organizer. It lets you pull specific data from your sheet (or other sheets) based on conditions you define.
Why it's useful: It lets you dynamically retrieve values that will become the basis of your conditional formatting. For example, you can use a query to pull a list of the top 10 salespeople, and then use that list to highlight their sales numbers.
Custom Formula:
Conditional formatting allows you to use formulas to determine if a cell should be formatted.
Why it's useful: Standard conditional formatting rules are useful, but custom formulas allow for very complex logic. You can use any Google Sheets formula inside of a conditional formatting rule.
Conditional Formatting:
This tool applies visual changes (colours, bolding, etc.) to cells based on the rules you set.
Why it's useful: by visually highlighting important information, it makes data easier to understand.
Indirect:
INDIRECT lets you build cell references from text strings. So, instead of A1, you could have "A" & 1 and INDIRECT would turn that into a valid cell reference.
Why it's useful:
Dynamic sheet names: If you have a sheet named "Sales2023" and you want to refer to it, but you might change the year, you could use INDIRECT("Sales" & A1) where A1 contains "2023".
Dynamic ranges: Similar to sheet names, ranges can be built from text.
How They Work Together:

QUERY retrieves the data: You use QUERY to get the specific information you need (e.g., a list of names, a range of values).
INDIRECT makes dynamic references: If needed, INDIRECT helps you point to the correct cells or ranges, even if their locations change.
The Custom Formula uses the results: Your conditional formatting custom formula uses the data from QUERY and INDIRECT to determine if a cell should be formatted.
Conditional Formatting applies the styling: Finally, conditional formatting applies the specified formatting to the cells that meet the criteria.