Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation

Hello,
I am studying in my Excel 2007 book about structured references and conditional formatting. If I choose to use conditional formatting from ribbon button and enter a formula, I know I can use references such as =F7>B7 for example, but can I use [Sale Price]>[Asking Price] from lets say the SalesData table for example. It doesn't work when I use the words [Sales Price], etc, but the actual reference =F7>B7 work fine. Perhaps my synatx is off.

1) Are structured reference names from tables allowed in conditional formatting formulas at all?
2) If so, what is the general syntax.

Just curious. Still can do what I want to do but my book doesn't address this and can't find anywhere.

Hi there,

I am currenly working on pay review spreadsheet and i am having a few problems trying to use the conditional formating and data validation option on the input cells, i would like to avoid any VBA as the sheets may need to go to 200+ people without macro enabled excel

1.I have two columns for inputing the salary increase,(N&O) The user will have a choice to enter the cash increase amount or the percentage increase amount however only one box must be completed. Through data validation I would like an error box to appear if both boxes are completed can anyone telkl me how this is done?

2. on the same sheet i also have two more columns for shift allowance increases (R&S), some of these allowances increase when the salary does and others are cash based. if the allowance is percentage based i would like to shade this out using conditional formating, additionally i would like to have an error box appear if any input is put in on these allowances but i would allow input for the cash based allowances

The below formula looks the shift on a seperate sheet and returns if it is cash or percentage based

=VLOOKUP($A13,SHIFT,16,0)=5

5 = %
0= cash

I have tried this formula in the conditional formating and data validation options but cannot get it to work

=IF(VLOOKUP($A13,SHIFT,16,0)=5


can anyone tell me where i am going wrong?

I'm trying to accomplish the same as in this thread HTML Code:

http://www.ozgrid.com/forum/showthread.php?t=145802

AAE

Lock/Unlock Cells Based On Validation Lists
VBA is subject to users choosing to enable macros - which they can elect not to do and thus defeat the code.

First - there is no validation list in cell F11 (or other cells) and the actual cell containing the value is F10 - you have the cells merged (bad idea - causes problems).
However, you can accomplish this without the use of VBA by using Conditional Formatting and Data Validation in conjunction with a helper cell.

Using column-Z as the help column, you could enter this formula: =F10="On Leave"
Which will return TRUE or FALSE; TRUE = F10 contains "On Leave"; FALSE = F10 does not contain it

Set cells J10:P11 to use Conditional Formatting, formula option, using this formula; =$Z10=TRUE
Select a gray fill color
Cells J10:P11 will be colored gray if Z10 = TRUE due to F10 containing "On Leave"

Next . . .
Apply Data Validation to cells J10:P11, using the Custom option and use this formula: =$Z10=FALSE
Set the Error Alert style to Stop and create a custom message if desired.
When Z10 = False it means cells F10 contains "Active" so users can make entries. If Z10 = False then cell F10 contains "On Leave" and Data Validation will prevent users from making entries





I applied this to my spreadsheet, but somehow when the cells are supposed to be cleared of the conditional formatting, only the top cell clears and the rest stays black (color I used).

I did all of it just the same.

except

=A1>1
=A2>1


then for range =$A$3:$E$24
=G1=FALSE then it colors everything black. then I clicked the box Stop if True. when the cell a1 has data, it only clears a3 and the rest stays black

Hi,

I'm trying to put together a spreadsheet that will format the colour of a row based on a value in a data validation list.

The problem is that I am limited by excel 2003. It only allows 3 conditional formats. I need to have a selection of 5 - 8 colours for the different options in the data validation list in column F. If possible I also want to have a conditional format in place for column I. If "h" is selected then it overrides the conditional formatting directed in column F and turns the row a specific colour. Am I asking Excel 2003 to do too much?

I suspect that a VBA code might sort this out easily, but I know little about VBA. I already have a VBA date stamp code in this spreadsheet, so the VBA code will need to be added to and run alongside the existing code. I need something that is relatively people proof and will allow for the insertion of new rows of data within the spreadsheet and at the bottom of the spreadsheet.

Any help is much appreciated. I have attached my spreadsheet so far in excel 2003 format if it is any use.

Many Thanks In Advance,
Joel

I've tried searching the forum for this answer, but with no luck. I have a cell (A112) that I want to reference Cell M112. If M112 equals ONLY True, then highlight cell A112 Black. I've had a lot of luck getting the Conditional Formatting wizard to work in simple cases like this, but for some reason it's not working this time around? I've used the "Use Formula to Format Cell", and applied the following rule:

Applies to Cell A112
=IF+M112=TRUE
I've also tried putting quotation marks around TRUE as well with no luck.

Any advise would be greatly appreciated.

Hi

I am a bit stuck. I have entered a conditional format so that the cell (H5) will turn red if it is below 75% of the value of another cell (F5). It turns amber if it is betwwen 75% and 100% of value of the cell (F5), and green if it is over 100% of the cell value (F5).

This all works fine. However I would like to copy this conditional formatting down the column from H5 to H300. When I attempt to do so it keeps the the same cell references in the formulas within the conditional formatting - therefore refering to cell F5.

I would like the formula to automatically change the same cell reference to the F column on that same row. E.g. If the formatting is in cell H15 I want the formula to refer to F15.

Is is possible for this to happen automatically as to do these changes by hand will take forever.

Cheers.

Simon.

All,
I am looking for a formula or macro to do the following

Copy the contents of a cell and then replace any reference to that cell in the rest of the spreadsheet with the contents of the cell.
E.g. cell f3 = a3*25
I want to then in all other cells which reference cell f3 in a formula replace f3 with a3*25

Can anyone help?? Please note that this will be needed for quite a large badly formatted spreadsheet with a large number of formulas contained within it.

Hi,

I need to add the "4 Ratings" icon set (displays 0, 1, 2, 3 & 4 shaded bars) to cells in a table, based on their value compared to a corresponding cell value. For example, if E16 >= BA17, show the icon (in E16) with 4 bars shaded; if E16 is >= BA16, show the icon with 3 bars shaded; if E16 is >= BA15, show the icon with 2 bars shaded etc.

The problem I have is that I need the conditional formatting to be applied to the range E16:T16, and the 'comparison cell' to be relative - that is, in cell G16, the criteria for 4 shaded bars is G16 >= BC16; in cell T16, the criteria is T16 >= BP16.

And the formatting for that whole range (E16:T16) needs to be applied to multiple ranges going down the page, each icon needing to be relative to other cells in the same way.

If I try to put the relative reference in the conditional formatting rule, as I would do for any other conditional formatting (cell color for example), I get the following error: "You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets"

Is there any way around this? I've googled the issue for a while and haven't found a solution I can implement. I can't place the icon in a cell of its own (which was one suggestion I found) due to spreadsheet constraints. If necessary, I'll have to put the conditional format in each individual cell, one at a time, which seems to be the only way to accomplish this - but that's going to be very time consuming, especially if I need to change the rule at a later date.

Here is the code I use in the worksheet object. You double click to insert a line below the selected line.

VB:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Dim LR As Integer 
    If Target.Column > 1 Then Exit Sub 
    Cancel = True 
    If MsgBox("Do you want to insert a new row below this row?", vbYesNo) = vbYes Then 
        With Target 
            Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 14)).Select 
            Selection.Offset(1).Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 
            Exit Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



That part works... however in column's E, J and M and N I have the following that won't copy and fill down accordingly...

E - Formula
J - Conditional Format
M - Formula
N - Data Validation

The formula's are as follows

E - =IF(ISBLANK(D10),(""),ROUNDDOWN((MINUTE(V10)-MINUTE(T10))/60,1))
M - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))

The conditional format in J a

If the cell value = "REP" then Orange text applies to =$J$10
If the cell value = "REC" then Red text applies to = $J$10

The Data validation in N is based off of cell M (a formula)

The data validation is "allow list" and source is "=INDIRECT($M$10)"

How do I copy these to the new lnserted line and then fill down to the end of my document, which will be no further than Row 150?

When I insert a line, the formula for instance in M goes like this

Line Above - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))
Inserted Line - (blank nothing there)
Line Below - =IF(ISBLANK(R11),(""),(CONCATENATE(R11,"",W11)))

I need it to fill this way.. for this column

Line Above - =IF(ISBLANK(R10),(""),(CONCATENATE(R10,"",W10)))
Inserted Line - =IF(ISBLANK(R11),(""),(CONCATENATE(R11,"",W11)))
Line Below - =IF(ISBLANK(R12),(""),(CONCATENATE(R12,"",W12)))

I hope I explained it well enough... basically I want to insert a line, and it copy and fill down the data accordingily...

thanks in advance!!

Hi,

Hope somebody can help, I have had a good search, but cant seem to find the correct answer.

I have a table within a sheet (F4:AH63), and I also have a list of initials (A68:A98), I wish to place a list validation into the table so that any of the initials can be selected, but my problem is, that I want the cell colour to change to match the colour of the cell that is in the list of initials.

Is there any way of doing this?

P.s. There are 8 different colours.

Regards