Excel - IF statement reference

Asked By Digisafenz on 15-Jun-12 02:10 AM
I have a spreadsheet with a drop downlist of choices and then another
cell that references to the drop downlist. the dropdown list details are
stored in a separate worksheet. I want the if statement to allow a
reference to the drop down list.

i.e. drop down list is in Column E then the if statement is in columns
I- R When I select a choice from the drop down list the columns I-R
check to see a match and return a result. The if statement is manually
configured as follows =IF(E46="Vehicle Signs",F46,0)  What I want to do
is be able to absoulute reference " Vehicle Signs" to the drop down
validation fields in the separate worksheet. This way if I change my
validation details, then the if calculation field changes also.

i hope this make sense. i can post up the spreadsheet somewhere if that
helps anyone come up with an answer for me.



GS replied to Digisafenz on 15-Jun-12 07:00 AM
Digisafenz wrote :

You can give the DV fields' range a local (sheet-level) defined name.
This can be the same name on every sheet so your formulas only need to
ref the correct sheet.

You create a local defined named by prefixing the name with the
sheetname wrapped in single quotes, and separated by the exclamation

Select the range to be named

Assuming "Sheet1"...
Type in the NameBox left of the FormulaBar:
..where 'UserChoices' should be similar to the column heading, and so
if your heading is "User Choices" then the defined name associates to
that. **Note that defined names can NOT include spaces, must begin with
an alpha character, and can NOT contain any special characters other
than _ (the Underscore). So.., the defined name could also be

In your formula example...

=IF(UserChoices="Vehicle Signs",F46,0)

To make the defined name column-absolute/row-relative you need to use
the Define name dialog...

Select a single cell in any col where the formula is to be used.
Open the Define name dialog and enter the name as described above.
Edit the RefersTo box as follows:
Remove the $ symbol preceeding the row number;
Leave the $ symbol preceeding the column label.

Alternatively, you can use column-absolute/row-relative refs this

=IF($E46="Vehicle Signs",$F46,0)


Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!