Data validation list without range reference

RandomizerTX

New Member
Joined
Jul 4, 2014
Messages
33
Hello everyone:

Column A contains numeric values. I need Cells in Column B to have a dropdown validation lists that allow selection of the positive or negative of the value in the adjacent Cell in Column A. So for example, if $A$1 contains 445, then the dropdown list for $B$1 should offer the options of 445 and -445.

Can I generate these pairs of choices without VBA and without having to store the two values elsewhere in the worksheet?

Thanks in advance.
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

If A1=445 then in B1, select DATA, VALIDATION, in Settings, select Custom and in the Formula, enter the following:

=OR(B1=A1,B1=A1*-1)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
I need Cells in Column B to have a dropdown ....

Can I generate these pairs of choices without VBA and without having to store the two values elsewhere in the worksheet?
Hope I am proved wrong, but I don't think so.

onlyadrafter's suggestion will allow the correct DV, but not provide a drop-down list. Similarly a custom formula of =ABS(B1)=ABS(A1) would do the same.
 

RandomizerTX

New Member
Joined
Jul 4, 2014
Messages
33
Hope I am proved wrong, but I don't think so.

onlyadrafter's suggestion will allow the correct DV, but not provide a drop-down list. Similarly a custom formula of =ABS(B1)=ABS(A1) would do the same.

Thanks to both of you. I need a dropdown list, because the two choices I need to see in Column B will actually refer to another worksheet.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
Then I think you are going to need to generate the two numbers somewhere in your workbook by formulas or use vba.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,608
Messages
5,548,975
Members
410,887
Latest member
sjohn627
Top