How to indicate required cells to be filled in

Yovanka Barnard

Good day,

I have different columns that does only pertain to certain rows.

How can I highlight/indicate which cells are required to be filled out?

Or can I lock cells which columns entry are not required for certain row items?

I tried conditional formatting. It takes a while though. And there is only AND functions and not OR conditions.

Please assist. Thank you.

Find more posts tagged with

Sheets

Project Management

Comments

Admin@PAT

HI Y

Can you show/share a sample of your sheet for more details. Conditional Formatting can be overwhelming but also very useful for highlighting/blanking cells and then there are conditions within CF.

It can be lengthy to set-up initially but it may be worth it to have the result you need/want to achieve.

Yovanka Barnard

Hi,

Attached is an example of what I want. Only certain columns needs to be populated as indicated by yellow.

So that the person filling in the information knows which columns needs to be filled out for each row item.

For example when you tender you are not required to flll in lead time, po number, etc. because it is not required then.

SS Info req example.jpg

J. Craig Williams

Assuming you want color when blank and no color when not blank:

Try this:

1. Color code the cells you want to be colored when they are blank.

2. Set conditional formatting on the column to be white background when not blank.

Sometimes I have had to do that in the other order to get it to work but haven't played with it long enough to determine why.

Hope this helps.

Craig

Yovanka Barnard

Hi Craig,

This will then only take away the color when text is filled in by them.

I still need to show the person populating the sheet in which cells he/she needs to capture info. Not all the columns pertain to all the rows.

J. Craig Williams

I'm not sure what you are missing then.

The color coding will show the cells that need to be populated.

That is done manuallly by you .

Once the user enters data, the color will automatically be removed.

What am I missing in your requirements?

Craig

Yovanka Barnard

I do not want to color code the cells manually to be filled in because new rows will be added and deleted and then I am not sure if I am going to keep track where I need to go and color because I am working with 10+ sheets.

I would prefer a automated solution for the color coding.

J. Craig Williams

How will the system know which ones to fill in?

Is it any blank cell?

How many columns do you have?

Craig

Yovanka Barnard

That's the thing. The system will not know. The only way I could find to automate it was to set up conditional formatting for if a row contained a specific word and then it should highligt a specific column. But it is going to take very long to set up the conditional formatting.

No it is not any blank cell. Please refer to JPEG image example above posted on 12/07.

I have 12 columns of which 9 requires input dependent on the row item.

J. Craig Williams

I've seen instances where someone has created dozens or more conditional highlighting rules. I tend to avoid them if possible as 16 colors that have meaning need to be defined somewhere to be useful. I digress.

If you leave the *Please fill in .... text in the cells you want to be updated, then each column has 1 conditional format - to change the background for that column.

If COLUMN_NAME contains "*Please" then apply this format to the COLUMN_NAME column.

Craig

Admin@PAT

Hi Y

I have flipped your thoughts around and found that maybe this will help - it will greatly reduce the lines of CF you will need - hope it makes sense, you can simply CF based on the colkumn it relates to rather than each individual line being different.

Capture SS.JPG