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.

Thanks

Digisafenz wrote :
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.

Thanks

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
character.

Example:
Select the range to be named

Assuming "Sheet1"...
Type in the NameBox left of the FormulaBar:
'Sheet1'!UserChoices
..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
'User_Choices'!

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
way...

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion