excel worksheet if statements required

Questions:

1. In D5, what direction should the arrow point (up, down, left, right)?
2. In E5 what character(s) do you want in that cell?
3. In F5 what character(s) do you want in that cell?

---

»bp

Hi Bill,
1- sorry the arrow i refer to is to be a tick symbol, so upwards
2&3 - sorry i dont understand your question, could you please give me more information?

I don't understand from the cell comments what values you want in E5 and F5.  You said:

"if the cell value in c5 is > than b5 then show in this column in bold red text with this background colour"

which doesn't state what is actually in those cells?  Is it one of B5 or C5?  Or the difference between them?  Or a plus or minus sign?  Etc...

---

»bp

hi Bill, i added into the cell comment what needs to be shown in this cell;
 "- if the cell value in c5 is > than b5 then show in this column in bold red text with this background colour" therefore the value should always =c5-b5, is it clearer now?

- but if the result is positive it will only show in column e, if the result between the 2 is negative then nothing will be shown in column e, only in column f.

See if this is close to what you were looking for.

yComparison-Job-Sheet_v1.1.xlsx

---

»bp

Hi Frank,

Please check the attached. The columns D, E, F and G contain Formulas, Custom Number Formatting and Conditional Formatting to get the desired output.
yComparison-Job-Sheet_v1.1.xlsx

hi Bill, very close thankyou, just 1 other thing, can i have the same green colour text for the word "same" used in column g if the values in col b&c are the same? and rather than an up arrow, is there a tick that can be used instead?
- can i just drag these formulas down the page so it applies to all colums d&g
- how can i put a block on the formulas in these columns d&g so they dont accidentally get erased.

I haven't seen the Bill's version but you can of course drag the formulas down the rows and the formatting will be copied down as well.
You may protect the worksheet after locking the formula cells.

Look at the Sheet2 and Sheet2 in the attached.
yComparison-Job-Sheet_v1.2.xlsx

hi subodh, thankyou for your help..

You're welcome Frank!

hi subodh, i can see you have hidden the formulas from the wksht, is there a reason you have done this?

- also the numeric values in columns d-f all need to be set to the same size of 12, and to 2 decimal places please.

Please look at the attached.
In an ideal scenario when the formula cells are locked and sheet is protected, I guess there is no point in allowing the users to see the formulas but you may change it as per your requirement.
If you unprotect the Sheet and select the formula cells and press Ctrl+1, the Format Cells window will be opened and you can change this setting as shown in the following image.

ok thanks..
just waiting on the final changes of the wksht to finalise..

hi Bill, very close thankyou, just 1 other thing, can i have the same green colour text for the word "same" used in column g if the values in col b&c are the same? and rather than an up arrow, is there a tick that can be used instead?
- can i just drag these formulas down the page so it applies to all colums d&g
- how can i put a block on the formulas in these columns d&g so they dont accidentally get erased.

• Color set on "same" now
• Changes arrow to check mark (tick)
• Yes on extending formulas, I did in the used area
• Columns with formulas protected

EE29137775.xlsx

---

»bp

hi Bill, thankyou but how do i 'unprotect the formulas' if i need to? it asks for a pwd?

also, ive noticed the grid lines are not all the same, see screenshot below, why is that? some are solid lines and others are dotted lines and some a combination of both..can we have a3 to h4 solid lines, the all others dotted lines?
- also can we widen columns d to g only to fit these number of spaces +2430.00

i have made a couple of minor changes to the sheet,
- renamed the 2 worksheets
- frozen row 4

Please make any future changes to this worksheet only.
yComparison-Job-Sheet_v1.2.xlsx

Password was left blank.

---

»bp

ok thankyou, so i can add my own pwd then..

my worksheet no longer works, has this something to do with renaming the sheet tabs below?

Looking at version 1.2 I don't see anything that is tab specific.  What exactly isn't working?

---

»bp

also, ive noticed the grid lines are not all the same, see screenshot below, why is that? some are solid lines and others are dotted lines and some a combination of both..can we have a3 to h4 solid lines, the all others dotted lines?
- also can we widen columns d to g only to fit these number of spaces +2430.00

Those changes have been made and protection enabled.

yComparison-Job-Sheet_v1.2.xlsx

---

»bp

hi, im trying to add a user button so i can clear all data from the worksheet, but the 'insert' function on the 'controls' tab has been disabled?
can you please enable all this functionality so i can add user buttons & attach macros to them?
- also can you please make the "same" text colour in column g the same as the tick symbol colour in column d?

ok thankyou.

thankyou to both of you for a solution to my worksheet idea..

Welcome.

---

»bp