Conditional Formatting Issue

[Harvey12]

Hi All,

I am currently setting up a file for my new department I have just joined. They are looking to make their excel documents more automated to save time.

One of my current tasks is to change cell colours depending on what is contained within the cell. I can easily conditionally format the cell to change colour depending on what is written the difficulty that I am struggling with is that I need that to change the colour of all of the cells in that row to the left of that cell.

For Example:

Cell K3 is the first cell containing the 'Status' of the information to the left of it. When someone types in the word 'Done' I need all the cells in row 3 to the left of K3 to highlight green!!

Can someone please assist.

Many thanks
Harvey

 

[Mike LH]

Hi,

Select A3:J3 then

Home tab | conditional formatting | new rule | use a formula to decide which cells to format and enter this formula


=$K3="Done"

Click the format button | fill tab and choose green and OK out. You can paint this doen with the format painter.

 

[Harvey12]

Hi Mike,

Many thanks for that assistance it is much appreciated.

As well as this, do you know of anyways of making what the type into that end / 'Status' column restricted, for example a drop down perhaps, with only a few things to choose between?

Many thanks
Harvey

 

[Mike LH]

Hi,

Yes we can do that. Put the items you want to be entered in a column of cells (say) Q1:Q4 then select k3 and

Data tab | data validation | data validation | In the 'Allow' dropdown select 'List' and then click the icon on the right of the 'Source' box and select your list of allowable inputs on the worksheet. Click the icon again and OK out.

Tip. I always colour the data validation dropdown cells so you know where thry are because when you're not in the cell the dropdown arrow doesn't display.

 

[Harvey12]

Hi Mike,

Once again many thanks for your help the file is exactly how they and I now want it! Much appreciated.

In addition to this I am now looking at a sub worksheet. All I am looking to do is get a cell to equal the contents of another cell.

In cell C4 I have a part number eg: 12345-2 (The "-2" is the issue number, which is variable)

I am looking to create a formula in cell A20, which will say equal 12345 (what I mean by this is get the cell to pull the part number removing the issue number)

Is that possible?

Many thanks
Harvey

 

[Harvey12]

*bump*

 

[Mike LH]

Hi,

Try this

=--LEFT(C4,FIND("-",C4)-1)

 

[Harvey12]

Hi,

I removed the "--" at the beginning and that seemed to work for me.

Many thanks

 

[Mike LH]

Hi,

It will work with or without the --

Without the -- the number returned will be a text value and the -- coerces it into a number.

 

[Harvey12]

Hi,

Okay thanks for your help