Need Dropdown or Cell Reference Based on T/F

[gbntpilot]

Hello all,

I've searched all over, but I can't find an answer to this problem, or if it's even possible without a Macro. I'll try to describe it as best I can.

I have data validation drop down lists in A1 and A2. The lists are in another sheet. Then I have a checkbox in A3, with a control cell in A4 (TRUE or FALSE). What I need is, if the checkbox control cell is TRUE, then I need the selected values in A2 to equal A1. If the checkbox is FALSE, I need to be able to select another value from the drop down in A2.

The general logic for A2 would be something like this (not meant to be literal): =IF(A4=TRUE, A2=A1, display the drop down list). Of course, that logic doesn't work because as soon as the formula runs and sets A2=A1, if the condition later becomes false, the previous list (data validation) is gone.

Any help would be greatly appreciated. I would like to avoid using macros if possible. Thanks in advance!

[Pete_UK]

Assuming you have a named range that feeds the DV in A2, you could set that up as a dynamic range so that if A4 is TRUE then the range is only one cell deep (i.e. A1) but if it is FALSE then the range reverts back to the full range you want to offer. Also, you can apply conditional formatting to A2 (to turn it red) if A2 is not within the named_range_2 values, to indicate that it needs resetting.

This does not actually make A2 the same as A1, but it is "almost" forcing the User to go to A2 and reset the value (and there will only be one value to choose from is A4 is TRUE).

Hope this helps.

Pete

[gbntpilot]

Thanks for the reply. That's not quite what I was looking for. Maybe I need to rethink how I'm doing this. This is a spreadsheet that will be used to report multiple flights for an aircraft charter company (one column per flight). The DV list is used to select the pilot. I'm using DV because other functions will use things like VLOOKUP to reference other information about the pilot in other parts of the spreadsheet. There will almost always be a minimum of 2-4+ flights per page with various statistics tracked about the pilot's fight time. So, if the user selected "Bob Smith" from the DV for the first flight, and then typed "B.Smith" for another flight, the numbers wouldn't add up. I'm doing this because it's very possible to report flights on this form with more than one pilot. So, I was trying to find a way to select a pilot from the DV, and just use a checkbox in the next flight entry to essentially say that next flight also had the same crew, without having to select a name from the DV again. Because so many flights would be entered on one form, I'm trying to find a way to save the user some time by not having to select the same crew from a DV list for every subsequent flight, by using the checkbox (saying it's the same crew), but maintaining the ability to select a different crew from a DV list for the subsequent flights.

Any ideas on how I could redesign or rethink this spreadsheet to make this idea work? Thank you!

[Pete_UK]

It's difficult to give any definitive advice without seeing the workbook (hint - attach a sample workbook: the FAQ describes how to).

Perhaps you can have an entry "same" in the named range for the second DV, so you can either select that or a different crew (I would imagine the crew is made up of several people, though, so I would have thought you would have several other drop-downs to select them if they are different).

Hope this helps.

Pete

[gbntpilot]

I think I may have to do something like that, or rethink my format. Thank you for your responses. Yes, the crew is made of multiple people. I was just keeping the example simplified in the forum since the solution I was looking for would just be duplicated multiple times. Thanks again!