MichelleNeedsHelp
New Member
- Joined
- Jul 7, 2023
- Messages
- 5
- Office Version
- 365
- Platform
- MacOS
Hi there,
I'm hoping this is relatively easy, but am not so sure. I'm not great with Excel formulas at all.
I have a massive spreadsheet that I'm trying to automate as much as possible. I have attached a picture.
In column C, it has the Country and Region of the client. There can be multiple jurisdictions that our client is in, but the two main ones are Canada and United States. I would like to populate the columns of H - W automatically using the information that is filled in to columns C - F.
For example, column H would be something along the lines of IF cell C3 = "Canada" AND cell D3 = "True" then Cell H3 = "True"
Another one could be IF cell C6 = "United States" AND cell D6 = "True" AND cell E6="True" then Cell P6 = "True"
The cells in C3 could contain multiple jurisdictions in each cell, so is it possible to have a formula look at that cell and check if the country is there?
There are also exclusions. So for the global, it would need to be something like IF cell C9 contains something other than "Canada" and "United States" then cell W9 = "TRUE"
And IF cell C7 is not equal to or does not contain "Canada" then cell T7 = "True"
I hope this is relatively easy and simple to do and I very, very much appreciate the help!!
I am posting the table below. In my sheet, the country/region column is column C for context with my examples above.
I'm hoping this is relatively easy, but am not so sure. I'm not great with Excel formulas at all.
I have a massive spreadsheet that I'm trying to automate as much as possible. I have attached a picture.
In column C, it has the Country and Region of the client. There can be multiple jurisdictions that our client is in, but the two main ones are Canada and United States. I would like to populate the columns of H - W automatically using the information that is filled in to columns C - F.
For example, column H would be something along the lines of IF cell C3 = "Canada" AND cell D3 = "True" then Cell H3 = "True"
Another one could be IF cell C6 = "United States" AND cell D6 = "True" AND cell E6="True" then Cell P6 = "True"
The cells in C3 could contain multiple jurisdictions in each cell, so is it possible to have a formula look at that cell and check if the country is there?
There are also exclusions. So for the global, it would need to be something like IF cell C9 contains something other than "Canada" and "United States" then cell W9 = "TRUE"
And IF cell C7 is not equal to or does not contain "Canada" then cell T7 = "True"
I hope this is relatively easy and simple to do and I very, very much appreciate the help!!
I am posting the table below. In my sheet, the country/region column is column C for context with my examples above.
| Country/Region | V3 Affiliations | Paid for premium templates? | Admin.us | Include Tag | V3Aff_CA_reg INCLUDE | V3Aff_CA_gov INCLUDE | V3Aff_CA_premium INCLUDE | V3Aff_USA_reg INCLUDE | V2Aff_CA_reg EXCLUDE | V2Aff_CA_gov EXCLUDE | V2Aff_CA_premium INCLUDE | V2Aff_USA_reg EXCLUDE | V2Aff_USA_premium INCLUDE | V3Aff_USA_premium INCLUDE | USA_premium INCLUDE | USA_reg INCLUDE | CA_premium INCLUDE | CA_gov EXCLUDE | CA_reg EXCLUDE | global_reg EXCLUDE |
| Canada + V3 | Canada + V3 | Canada + V3 + Premium | US + V3 + | US + Premium | US | Cda + Premium | if no CDA then | If no CDA then | ||||||||||||
| Canada | TRUE | #CALC! | ||||||||||||||||||
| United States | TRUE | V3Aff_USA_reg INCLUDE V2Aff_CA_gov EXCLUDE V2Aff_USA_reg EXCLUDE V2Aff_USA_premium INCLUDE USA_premium INCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | |||||||||||
| Canada | TRUE | V2Aff_CA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | ||||||||||||||||
| Canada India United States | TRUE | V2Aff_CA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | ||||||||||||||||
| United States | TRUE | TRUE | V2Aff_USA_premium INCLUDE USA_premium INCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | |||||||||||||
| Canada International United States | TRUE | V2Aff_CA_premium INCLUDE V2Aff_USA_premium INCLUDE USA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | TRUE | TRUE | ||||||||||||||
| Canada International United States | TRUE | V2Aff_CA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | ||||||||||||||||
| Canada United States | TRUE | TRUE | V3Aff_CA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | |||||||||||||||
| United States | TRUE | V2Aff_CA_reg EXCLUDE V2Aff_CA_gov EXCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | ||||||||||||||
| Canada International United States | TRUE | V2Aff_USA_reg EXCLUDE | TRUE | |||||||||||||||||
| Canada | TRUE | V2Aff_USA_reg EXCLUDE | TRUE | |||||||||||||||||
| Canada | TRUE | V2Aff_CA_premium INCLUDE V2Aff_USA_reg EXCLUDE CA_premium INCLUDE | TRUE | TRUE | TRUE | |||||||||||||||
| Canada International United States | TRUE | V2Aff_CA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | ||||||||||||||||
| Canada International United States | TRUE | #CALC! | ||||||||||||||||||
| United States | TRUE | V3Aff_USA_reg INCLUDE V2Aff_CA_gov EXCLUDE V2Aff_USA_reg EXCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | TRUE | |||||||||||||
| Canada United States | TRUE | V2Aff_CA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | ||||||||||||||||
| United States | TRUE | V2Aff_CA_gov EXCLUDE V2Aff_USA_reg EXCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | ||||||||||||||
| Canada International United States | TRUE | #CALC! | ||||||||||||||||||
| Spain United States | TRUE | V2Aff_CA_gov EXCLUDE V2Aff_USA_reg EXCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | ||||||||||||||
| International United States | TRUE | V2Aff_CA_gov EXCLUDE V2Aff_USA_reg EXCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | ||||||||||||||
| Canada United States | TRUE | V2Aff_CA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | ||||||||||||||||
| United States | TRUE | V2Aff_CA_gov EXCLUDE V2Aff_USA_reg EXCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | ||||||||||||||
| Canada United States | TRUE | TRUE | V2Aff_CA_premium INCLUDE V2Aff_USA_reg EXCLUDE V2Aff_USA_premium INCLUDE USA_premium INCLUDE CA_premium INCLUDE | TRUE | TRUE | TRUE | TRUE | TRUE | ||||||||||||
| Canada United States | TRUE | V2Aff_CA_premium INCLUDE V2Aff_USA_premium INCLUDE USA_premium INCLUDE | TRUE | TRUE | TRUE | |||||||||||||||
| United States | TRUE | TRUE | V2Aff_CA_gov EXCLUDE V2Aff_USA_reg EXCLUDE V2Aff_USA_premium INCLUDE USA_premium INCLUDE CA_gov EXCLUDE CA_reg EXCLUDE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE |
