• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a "helper" column

D

Dexter1759

New Member
All,

I'm currently setting up a template in Excel 2007. On a worksheet I have an Excel Table that the user can add rows to. I'd like to include a "helper" column on this table that shows some red text to the user indicating if there's something they need to do/resolve.

A couple of example of the help text and conditions would be:

  • "Duplicate value in column X" - where COUNTIF of current value in X is >1.
  • "Data is required in column Y" - where a specific column is blank. This could even be reliant on the value of another column, e.g. If Column Z is "this", Column Y cannot be blank, display message.
Obviously for the two rules above this is quite and easy nested IF statement, however, I'm trying to think of a scalable way of doing this, so that it's not a chore to check conditions.

Also, I don't mind if the text shown is either the first condition passed, or a concatenation of all conditions.

I hope I've explained myself fully enough, if not please feel free to nudge me for clarification.

I should say as well that I'm looking for a way to do this with formulae rather than VBA, due to performance concerns.

Many thanks,
Dex
 
Hi Dexter ,

At this stage where it just a concept , what kind of help are you looking for ?

Probably , once you have listed all the rules that you want to implement , we can help , but I am not sure what kind of help can be offered at this stage.

You know your data best , and only you can say what rules need to be implemented ; just put down all of them , and then we can see how best they can be implemented.

All I can say is that since this will in itself be a helper column , why overburden it ? Why not have many helper columns , each of them dealing with one rule ? The error messages can be a lookup table listing the error description / user alert associated with each error number.

Narayan
 
Hi Dex,

Plz see attachment, in case it doesn't help, plz upload a sample file.
 

Attachments

  • Dax.xlsx
    8.3 KB · Views: 3
@NARAYANK991, You're right I am at the early stages, I guess I was just after some thought's/inspiration on a simple way to do it.

Having multiple helper columns would add too many columns, however, your post has given me an idea. I will use a helper column for each message, but hide them and concatenate them (or pick the first one, not sure yet) into a single visible helper column.

So, thank you, your post has provided the inspiration for a workable, flexible solution.

Dex
 
Hi Dexter ,

Of course , the better method would be to use Data Validation where ever possible , so that :

a. Through the input message box , the user can be alerted to the input requirements , for instance where dates have to be entered , what kind of formats are they to be entered in.

b. If cells are left blank or if the entered data is invalid , the user can be alerted and the entered data can be rejected even before the user moves on to the next cell.

Narayan
 
@NARAYANK991, completely agree when it's based solely on the value of the cell, but I'm unaware of a way to use Data Validation when there's a condition on an adjacent cell (e.g. If cell to left ="XYZ", this cell cannot be blank).

I'm going ahead with prototyping the multiple hidden headers, and stuck on the concatenation of a range of cells. I forgot that the CONCATENATE function needs each cell individually, rather than a complete range.
 
FYI - I have succumb to having to create a custom function for the concatenation of a range and will see how it affects performance.
 
You must log in or register to reply here.
Back
Top