Hi,
I'm new to 'the deeper excel' (beyond pivot tables and vlookup) in the last two months as I work on a side project for work, and finally throwing in the towel and creating an account here to ask a question.
For the purposes of this question, I have a worksheet titled "Customer Entry" which has a column that I would like to have conditional formatting applied to if the entered value is "less than n characters" or "greater than y characters". This sheet also has a cell at B1 that displays the customer.
n and y are declared on a second worksheet called "Customer1" in a table called "customer1_References" which is 8colx4rows including headers on both the columns and rows. So n is in customer1_references 2,2 and y is in 3,2 (row,column). Another fun fact is that through a userform I can create any number of these worksheets, named "customer1","customer2","customer3", etc etc and so n and y can be different for each. The names of the sheets are pulled from that B1 so they match perfectly.
To be able to set conditional formatting for any value that has a length less than n, and greater than y, and has n & y changing depending on which customer is selected, what does that conditional formatting formula need to look like?
I'm nervous this isn't nearly enough detail....but here are two things I have tried:
the isblank() portion prevents any formatting for blank entries. Then it's looking at the length of the value in the entry column (B3) and deciding either too short OR too long....based on the sheet name pulled from B1 on that sheet and the value on that customer sheet. Customer1!P11 is n, and Customer1!P12 is y. This formula is accepted by Excel but doesn't do anything.
instead of referencing a specific sheet/range combo, this is using Index() and the named table as an array and hopefully looking at the value at a particular intersection (2,2) & (3,2) which I think should be the equivalent of P11 and P12 respectively. However, Excel does NOT accept this formula. I was able to take just the "Index" portions of this formula and put them into random cells and I was able to confirm that they do return the correct number for n and y....just doesn't seem to like it in the conditional formatting screen. The next hurdle would be getting "customer1" in those table names to be replaced with a reference to the B1 cell so it would change congruently.
What am I missing? Thanks in advance.</index(customer1_references,2,2),len(b3)></indirect("'"&b1&"'!"&"$p$11"),len(b3)>
I'm new to 'the deeper excel' (beyond pivot tables and vlookup) in the last two months as I work on a side project for work, and finally throwing in the towel and creating an account here to ask a question.
For the purposes of this question, I have a worksheet titled "Customer Entry" which has a column that I would like to have conditional formatting applied to if the entered value is "less than n characters" or "greater than y characters". This sheet also has a cell at B1 that displays the customer.
n and y are declared on a second worksheet called "Customer1" in a table called "customer1_References" which is 8colx4rows including headers on both the columns and rows. So n is in customer1_references 2,2 and y is in 3,2 (row,column). Another fun fact is that through a userform I can create any number of these worksheets, named "customer1","customer2","customer3", etc etc and so n and y can be different for each. The names of the sheets are pulled from that B1 so they match perfectly.
To be able to set conditional formatting for any value that has a length less than n, and greater than y, and has n & y changing depending on which customer is selected, what does that conditional formatting formula need to look like?
I'm nervous this isn't nearly enough detail....but here are two things I have tried:
Code:
=IF(ISBLANK(B3)=TRUE,"",OR(LEN(B3)<indirect("'"&b1&"'!"&"$p$11"),len(b3)>INDIRECT("'"&B1&"'!"&"$P$12")))the isblank() portion prevents any formatting for blank entries. Then it's looking at the length of the value in the entry column (B3) and deciding either too short OR too long....based on the sheet name pulled from B1 on that sheet and the value on that customer sheet. Customer1!P11 is n, and Customer1!P12 is y. This formula is accepted by Excel but doesn't do anything.
Code:
=if(isblank(B3)=TRUE,"",OR(LEN(B3)<index(customer1_references,2,2),len(b3)>INDEX(Customer1_References,3,2)))instead of referencing a specific sheet/range combo, this is using Index() and the named table as an array and hopefully looking at the value at a particular intersection (2,2) & (3,2) which I think should be the equivalent of P11 and P12 respectively. However, Excel does NOT accept this formula. I was able to take just the "Index" portions of this formula and put them into random cells and I was able to confirm that they do return the correct number for n and y....just doesn't seem to like it in the conditional formatting screen. The next hurdle would be getting "customer1" in those table names to be replaced with a reference to the B1 cell so it would change congruently.
What am I missing? Thanks in advance.</index(customer1_references,2,2),len(b3)></indirect("'"&b1&"'!"&"$p$11"),len(b3)>
