Using a value in a table to set a "max length" conditional formatting rule in a cell on another sheet.

gazook89

New Member
Joined
Aug 14, 2018
Messages
1
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:

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)>
 

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top