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

Truman46

New Member
Joined
Oct 8, 2015
Messages
25
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Okay its taken me awhile to get this sorted but I've included part of my table below.

In column I, J and K I want to create a data validation list so we can choose these items from a list. But once that item is used it cant be used again and dissapears from the list until the item is returned. So if we use battery BP001 and SR0001 and SR0002 tehn those items dissapear from the list and cant be selected again until the items are returned.

Is there anyway to do this?

Thanks


Excel 2012
ABCDEFGHIJK
1CLIENTJOB NUMBERDATE OUTLOW VOLTAGE TYPEPOWER SUPPLY (PLUG IN)IP44 HOLDER25 METRE EXTENSION LEAD10 AMP DOUBLE ADAPTERSOLAR PANEL LEFTSOLAR PANEL RIGHTBATTERY CODE
6Gorman & Kelly83305421/08/2015SOLARN/ASL0001SR0001PB0003
7FLETCHERS52847131/08/2015SOLARSL0005SR0005PB0001
8Barry Plant Northcote5287943/09/2015SOLARSL0007SR0007PB0012
PRODUCT DESPATCH
 
Last edited:
Upvote 0
I thought of multiple strategies to come up with a solution, the following is the approach i have chosen, which by all means not perfect and can be optimized.

first this will be my canvas or table sheet if you will:

25BirFwM.png



the "no." column is important for conditional formatting and for setting the dynamic ranges my model needs, here this particular sheet dynamic ranges:

Code:
no.
=OFFSET(Table!$A$7,,,COUNT(Table!$A:$A))

MyTable (for reference only not vital)
=OFFSET(Table!$A$7,,,COUNT(Table!$A:$A),13)

the conditional formatting is based on the MOD of each incremental value on column no. with a divisor of 2 (e.g. if the MOD of A7 is 1 color it the line with light gray), the conditional formatting is set on range $A:$M

my strategy is that you can insert through a button and a combobox the tag of the Solar panel into the active relative cell, once inserted, that Solar panel will not be available until returned (e.g. the date in column value is not blank). same concept applies for the Battery.

i did not go for the strategy of feeding the selected Panel or battery tag into the last row relative cells in the table automatically . why ? that will prevent you from modifying previous inserted tags + will encourage manual entry for adjustments which might cause a used tag that is not returned yet to be inserted, which is against the logic of the problem in hand.

from the picture above you can see you have two combo boxes (one for the solar panels and the other for the battery) select the relevant cell go to the box and select the panel/battery tag and click the adjacent button to insert the tag. the combo box will refresh and update automatically to exclude inserted tags from the list unless the device is returned back (indicated by a non empty cell in the relative DATE IN column)
 
Upvote 0
now for the engine page, the one that drive the table page:

25C9Mkpc.png



i have to sections for the calculations one for the Solar panels and the other for the battery. they are identical in term of construction so i will explain one only.

on column C i have the list of all the Solar panel that i have created.

column D is a sum product equation that relates to the Table page where i will look whether that solar panel is used in the left or right socket and whether it is returned or no, the value the following formula will give is either 1 or 0:

Code:
=SUMPRODUCT((OFFSET(no.,,9)=C5)*(OFFSET(no.,,12)=""))+SUMPRODUCT((OFFSET(no.,,10)=C5)*(OFFSET(no.,,12)=""))

where (no.9) is the range we have defined before in the table page

Column F is used to give an index number for the Solar panels that are currently available (have a SUMPRODUCT vaue of 0 from Column D):

Code:
=IF(D5=0,COUNTIF($D$5:D5,"=0"),"")

Column G is the list of all avaliable Solar panels that i can issue to my clients, it is populated with the following formula:

Code:
=IFERROR(INDEX(SolarList,MATCH(ROWS($F$5:F5),OFFSET(SolarList,,3),0)),"")

where SolarList is a dynamic range i defined in the Name Manager to reflect the list of all solar panels i have created in Column C: SolarList

Code:
=OFFSET(LIST!$C$5,,,COUNTA(LIST!$C:$C)-1)

now i create a dynamic range on the final list of available Solar Panels for reference purposes: SolarListF

Code:
=OFFSET(LIST!$G$5,,,COUNTA(LIST!$G:$G)-1)


repeat the process for the battery section, do all what we did for the Solar panels Section. i End by Dynamic Range for the Battery available Named BatteryListF


next i discuss the VBA
 
Upvote 0
I have two Combo Boxes in My table page that i need to populate, what is the source ? the VBA code will handle this:

Code:
Private Sub SLI_Change()

SLI.ListFillRange = "SolarListF"


End Sub


Private Sub BI_Change()


BI.ListFillRange = "BatteryListF"
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)


SLI.ListIndex = 0
BI.ListIndex = 0


End Sub

one Sub for the Solar panel Combo, it fills the combo from the Dynamic Range created Titled SolarListF

the other Sub does the same for the Battery Combo, sourcing the input from the range called BatteryListF

the final Sub is related to the Table Worksheet, whenever there is a change in the sheet (entry or anything else) the worksheet will refresh the Combo boxes and index on the 1st entry in their ranges.

insert all these subs in the Table Sheet preferably not another module.

The buttons related to inserting the combo box selection into the active cell has a straight forward code:

Code:
Sub SolarInsert()

ActiveCell = Sheets("LIST").Range("G2")


End Sub




Sub BatteryInsert()


ActiveCell = Sheets("LIST").Range("q2")


End Sub

where Cell G2 and Cell Q2 in the second (engine) sheet are linked with the each combo box respectively (the combo boxes are created and linked through their properties with the Cells mentioned).

Finally, i do not want a manual entry on the columns related to my Solar panels and Battery tags, i only can insert tags in those cells or replace them from the Combo box selection. to do this ensure that they are locked while other cells that you have freedom to edit unlocked from their cell property window.

i created two boxes in the Table Sheet (or call them buttons) to protect and unprotect the sheet. that shall not affect the capablity of the Macro to insert Solar panels and battery tags from the combo boxes. here is the code for the two boxes:

Code:
Sub prevententry()



Sheets("table").Shapes("Rectangle 1").TextFrame.Characters.Font.ColorIndex = 56
Sheets("table").Shapes("Rectangle 2").TextFrame.Characters.Font.ColorIndex = 2


Sheets("table").Protect , UserInterfaceOnly:=True


End Sub




Sub unprevententry()


Sheets("table").Shapes("Rectangle 1").TextFrame.Characters.Font.ColorIndex = 2
Sheets("table").Shapes("Rectangle 2").TextFrame.Characters.Font.ColorIndex = 56


Sheets("table").Unprotect


End Sub


and that is my model for the problem, as i have mentioned it could be optimized or it could have another approach all together.

i have tried to help you using my existing knowledge which is still in infancy mode :)

may the force be with you. (y)
 
Upvote 0
Thanks heaps for all your help on this one. A lot of it is way over my head but Im sure I will work it out when I get a chance to sit down and read through it all and work out how it all works.

Your help on this is Very much appreciated
 
Upvote 0
thanks.

try to visit the forums regularly and check the questions and solutions posed; try to re-do them on your machine, that will help in the learning curve required.

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.
 
Upvote 0
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'm just describing the set-up for battery code but it could be duplicated for other items.
You probably already have a column to enter the returned date but did not show that column in your screen shot. I'm assuming it is column L

1. In S2 and downwards, list all the battery codes possible.
2. T1 houses a zero and the T2 formula is copied down to the end of the battery code list
3. U1 is a stand-alone formula
4. U2 is copied down as far as fas column T was.
5. Once you get it all working, columns S:U could be hidden if you want.

From your screen shot it looks like your data entry might start in row 6, but in any case select cells in column K from wherever the first battery code might go down as far as you want and apply the Data Validation shown.
In the Data Validation dialog -> Settings tab -> Allow: List -> Source: put the formula shown in the Data Validation section below my screen shot.

Now, as you enter battery codes in column K from the drop-down list, those codes will not appear in the list for the next cell etc. However, once the item has a date in column L it should re-appear in the drop-down list.

Excel Workbook
KLMNOPQRSTU
1BATTERY CODEDATE INBattery Codes0$U$2:$U$11
2PB0001 PB0002
3PB00021PB0004
4PB0003PB0005
5PB00042PB0006
6PB0003PB00053PB0008
7PB000114/09/15PB00064PB0009
8PB0012PB0007PB0010
9PB000616/04/15PB00085PB0011
10PB0007PB00096PB0013
11PB0001PB00107PB0014
12PB00118
13PB0012
14PB00139
15PB001410
16
Diminishing drop-down
#VALUE!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top