Link to home
Start Free TrialLog in
Avatar of mlagrange
mlagrangeFlag for United States of America

asked on

Possible to have formula in the same cell that has data validation?

Hello - I've got this data input spreadsheet; one cell has a data validation list that could have either 1 row, or several rows; if there is only one row in the range feeding the DVL, I would like to automatically populate this cell with that value. Otherwise, the user has to pick from the list.

There is a 2nd issue; I don't want anything in that cell AT ALL, until the user enters a value in another cell.

So given:
the initial input cell is A1
the 2nd cell with the DVL is B1
the values for the data data validation list are on another worksheet in that workbook called "INPUTS", in column A, with a header of "Project Numbers", followed by 5-digit project numbers below that
 
I tried this formula in a test cell:
=IF(ISBLANK(A1), "",IF(COUNTIF(INPUTS!A:A, ">1") = 1, INPUTS!A2:A2), "")

So, if the user has not entered anything in A1, the B1 would be blank
If the user has entered something in A1, and there is only one project number in the range of INPUTS!A:A, then it would pull that value
Otherwise it would leave B1 blank, and the user would have to pick a value from the list

This works fine off in a "test cell", but it won't let me put that formula in B1, which has the data validation specified

Is that the way it is, or is there a way to get around this?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Shanan212
Shanan212
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mlagrange

ASKER

Yep, that's what it came to...

Thanks