Selecting items only once in a list until that item is returned.

[Truman46]

Hello, Im trying to setup a spreadsheet where I can use a list to select items only once until that item is returned.

We have batterys and solar panels that go out with our solar lighted real estate boards. The batteries and solar panels have numbers and I want to be able to add the battery and solar panel numbers used with the board number in a spreadsheet, but then that battery and solar panel number cant be used again until the board comes back and the items are returned.

Not sure where to start with this one so any help appreciated.

 

[Peter_SSs]

i tried to send you some books and references links that may help you in your inbox but it is full; so clear some space if possible :wink:

there are some Excel Gurus who offer private streamlined training material that are focused on specific areas like dashboards, accounting ...etc. just clear your inbox so i can send it to you.

I'm sure many readers of this thread would be interested in that information, and since we are a public forum it would be good if you made that information public too.

 

[SpreedsheetCrusader]

Here's another option to consider that doesn't require any vba code, but does use some helper columns. I've used columns S:U as helpers but they could be any columns.

I really liked your solution peter especially that it avoids VBA which is a real plus.

I wish you think about distilling your strategies and thinking model into a book or some organized video series /web posts. strategies to tackle spreadsheets problems / design are critical and good ones need to be passed to the next generation of young spreadsheet dwellers from the gurus


one thing about your approach is that when i copy the data validation into the next cell (next row) I retain the same value as of the previous cell unless i activate the cell by clicking were the list will show the updated items/batterys not used. i.e. i need to click into the list after copying so i do not see the used items.

I'm sure many readers of this thread would be interested in that information, and since we are a public forum it would be good if you made that information public too.

Errr... ok probably i will post that in another thread.. but you know what ...

 

[Peter_SSs]

one thing about your approach is that when i copy the data validation into the next cell (next row) I retain the same value as of the previous cell unless i activate the cell by clicking were the list will show the updated items/batterys not used. i.e. i need to click into the list after copying so i do not see the used items.

I don't understand. If you copy the cell and Paste Special (Validation) into an empty cell(s), which is what I thought you were describing, then those cells should inherit the Data Validation but remain empty themselves.

 

[SpreedsheetCrusader]

Ahaa

I am not copying and pasting with paste special, am just copying and pasting normally that explains it.

I will try the paste special with validation.