I would like to ask for some help with functionality that I want to introduce in my Excel file: I want to have some cells active/inactive depending on a value in another cell. Inactive means that the cells will be visible but they will be grey shaded and people will not be able to write in them.
I have a number of indicators with targets and results, and depending whether the result is above or below the target I want some other cells to become active and people can introduce information therein.
Let's take the following example:
Column A – Indicators
A2 – Number of cars produced
A3 – Number of people employed
A4 – Productivity ratio
Column B – Target
B2 – 1000
B3 – 50
B4 – 5%
Column C – Results
C2 – 1100 (the objective is that it should be higher than the target)
C3 – 54 (the objective is that it should be lower than the target)
C4 – 4.91% (the objective is that it should be higher than the target)
I have also cells (D2:L4) that are inactive when the results are fine. But when they are not in line with the target (in my case C3 & C4), the respective cells (D3:L3 and D4:L4) should become active and users be able to include additional information in the cells D2:L4. I would like the activation of cells to happen automatically, i.e. without the need to press Run Macros or anything else.
I tried with Data Validation but this is not exactly what I am looking for. From what I read I understand that I have to use VBA but unfortunately, I am not expert in this. I am not sure also how the protection works in this case – I would like after users introduce the information in cells D2:L4 that it is available to others for editing and copy/paste.
I would really appreciate some help with all this.
I have a number of indicators with targets and results, and depending whether the result is above or below the target I want some other cells to become active and people can introduce information therein.
Let's take the following example:
Column A – Indicators
A2 – Number of cars produced
A3 – Number of people employed
A4 – Productivity ratio
Column B – Target
B2 – 1000
B3 – 50
B4 – 5%
Column C – Results
C2 – 1100 (the objective is that it should be higher than the target)
C3 – 54 (the objective is that it should be lower than the target)
C4 – 4.91% (the objective is that it should be higher than the target)
I have also cells (D2:L4) that are inactive when the results are fine. But when they are not in line with the target (in my case C3 & C4), the respective cells (D3:L3 and D4:L4) should become active and users be able to include additional information in the cells D2:L4. I would like the activation of cells to happen automatically, i.e. without the need to press Run Macros or anything else.
I tried with Data Validation but this is not exactly what I am looking for. From what I read I understand that I have to use VBA but unfortunately, I am not expert in this. I am not sure also how the protection works in this case – I would like after users introduce the information in cells D2:L4 that it is available to others for editing and copy/paste.
I would really appreciate some help with all this.