Inserting IF Function and depending on answer have cell populate with another cell.

[Rhiannon25]

Hi Forum

I am having some trouble with my excel spreadsheet (I have attached an example of the spreadsheet on this thread).

Basically what I want to happen is on 'Sheet 2' (cell B3) I would like to have a formual so that if 'Sheet 1'(Cell B5) equals a certain number it will populate with another cell (e.g. Cell B22). For Example::

If 'Sheet 1, B5' = 0 then 'Sheet 2, B3' will populate with 'Sheet 2, C21' (the cross in the red box)
If 'Sheet 1, B5' = 1 then 'Sheet 2, B3' will populate with 'Sheet 2, C22' (the tick in the green box)
If 'Sheet 1, B5' = 2 then 'Sheet 2, B3' will populate with 'Sheet 2, C23' (the tick in the green box)
If 'Sheet 1, B5' = 3 then 'Sheet 2, B3' will populate with 'Sheet 2, C24' (the tick in the green box)
If 'Sheet 1, B5' = 4 then 'Sheet 2, B3' will populate with 'Sheet 2, C25' (the tick in the green box)
If 'Sheet 1, B5' = 5 then 'Sheet 2, B3' will populate with 'Sheet 2, C26' (the tick in the green box)
If 'Sheet 1, B5' = 6 then 'Sheet 2, B3' will populate with 'Sheet 2, C27' (the tick in the green box)

Now I beleive you can do this with an IF Statement and this is the one I found would populate it correctly:

=IF(Sheet1!B5=0,Sheet2!C21,IF(Sheet1!B5=1,Sheet2!C22,IF(Sheet1!B5=2,Sheet2!C23,IF(Sheet1!B5=3,Sheet2!C24,IF(Sheet1!B5=4,Sheet2!C25,IF(Sheet1!B5=5,Sheet2!C26,IF(Sheet1!B5=6,Sheet2!C27)))))))

However when I try to drag this formula down to populate all the other boxes it moves the 'Sheet 2, C21-C27' cells down to 'Sheet 2, C22-C28' and so on. How do I get this not to happen??

Because I need to do 4 weeks for each month and if I have to populate it manually and change it, it will take forever.

Also how do I get the cell to include the colour??

Any help on this would be greatly appreciated Inspection Schedule Example.xls

Regards
Rhiannon

[rcm]

the $ signs tell excell that it remains fixed. try and copy the formula across the columns and to all rows...

Please Login or Register  to view this content.

[Rhiannon25]

Thank-you RCM Thats works perfectly. Any Idea on the colour??

[rcm]

yes, I created a rule through conditional formatting

[Rhiannon25]

That seems to get the cell the the 'Cross' is in the show up red as needed, however the ticks are not showing up in a green box?

[FDibbins]

Hi

Try this shorter version...

=VLOOKUP(D3,$B$21:$E$27,2,FALSE) Copied down

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select "format only cells that contain"
4. enter û format fill red

Repeat 3 and 4 for "format only cells that do NOT contain"

[rcm]

I took @FDibbings suggestion, corrected the right column check, autofitted the width so take a look

[Rhiannon25]

Hi FDibbings

I only have access to excel 2003 and can only inpout 3 conditional formatting options.

and rcm

I opened your spreadsheet and the spreadsheet just had pictures of scissors in the coloumns where the ticks and crosses are meant to be

[rcm]

I'm sorry about that both it is the different font type between 2010 and 2003. try to change the font type and you'll be back to your original plan.

[Rhiannon25]

Ok, I changed it to a normal font and it is coming up with an 'Invalid Name Error'?

[rcm]

did you save the file in 2003 version, compatability issues sometimes give problems

[Rhiannon25]

When I click on it it comes up with open or save. I have tried hitting open and save (when I save it only gives me two options in the 'save as type' option. One is all files and the other eis excel worksheet. I only have 2003 version so how do I save it as this??