How to have a list that depopulates when its data is entered in another cell

[oakie45]

I am a novice at excel so please excuse this if this is a basic question. I need to create a list with our unit numbers that will depopulate and repopulate depending if they are entered else where in the same worksheet. We would use it to see which units are at our yard/location( in the list) or at job sites(entered in other columns). It would be really nice for it to be visible all the time. Thanks!!

Matt

[noboffinme]

Hi oakie45,

Can you post an example file explaining what you need?

It would make it easier to understand your question.

Thanks

[oakie45]

Unit Job Accounting.xls


I have attached a file with a very basic format of what I plan to accomplish. I need to know how to have a list in a cell that will depopulate or repopulate automatically depending on what is in other cells. Thank you very much for taking the time and trying to help me.

Matt

[noboffinme]

Hi Matt,

Paste the below Formula in Cell 'A24'.

=CONCATENATE(CONCATENATE(C2," ",C3," ",C4," ",C5," ",C6," ",C7," ",C8," ",C9," ",C10," ",C11," ",C12," ",C13," ",C14," ",C15," ",C16," "),(CONCATENATE(C17," ",C18," ",C19," ",C20," ",C21," ",C22," ",C23)))

This solution is a bit rough but will do what you want.

There's a limitation of the number of arguments you can use with the Concatenate Function, so I have worked around it by nesting 2 Concatenates inside an outer Concatenate function.

Let me know if this isn't what you wanted, Cheers

[oakie45]

Unit Job Accounting.xls

Thank you for the help but that did not do what i was looking for. What the concatenation did was put what ever was entered in the "unit numbers" column into the "unit in the yard" column. I would like to have a list of all our units in the "units in the yard" column that automatically updates as the units are taken to jobs. They will then be entered into the "unit numbers" column, this is when I would like the list to be updated automatically if possible. When the units are brought back I would delete the entry in the "unit numbers" column and would want the unit automatically placed back in the list in the "unit in the yard" column. I have attached an updated file with the concatenation and some added notes. Thank you again for your help.

[noboffinme]

Okay, you will need to be more specific so I can help.

Is the purpose of this file to quickly show which Units are still available?

[oakie45]

Okay, you will need to be more specific so I can help.

Is the purpose of this file to quickly show which Units are still available?

Yes exactly. So sorry I couldn't put it into such few words. I thought if I tried to explain it more detail the better.

[noboffinme]

Thanks oakie45,

Your detail was useful but it is always a good idea to also describe what you're trying to do as it helps Forum users think in the same way you are.

[oakie45]

I have played around with the data validation drop down list but I don't think I will be able to use this as I need the source data to update as well. I was wondering if it is possible to minus a string in a cell from a string another cell. For example, say I have C-2003,C-3601,C-3602,AH-202 in A:1 and then I enter C-2003 in B:1. I want A:1 to then change to C-3601,C-3602,AH-202. Also if I were to later delete the data in B:1, C-2003, then A:1 would return to C-2003,C-3601,C-3602,AH-202.

[noboffinme]

Hi oakie45,

I think I've got what you need using a combination of VBA & Excel Formulas.

I'm using 2 Worksheets, 'Units Available' (Your Worksheet) & 'Unit Numbers', which is the Reference info for ALL your current Units.

NOTE: you can add or remove Units to this list in Column 'A' in the 'Unit Numbers' Worksheet as your list of Units grows or shortens.

How it works - The VBA code searches Column 'C' of the 'Units Available' Worksheet one by one & compare it to Column 'A' of the 'Unit Numbers' Worksheet, the Formula in Column 'C' of the 'Unit Numbers' is then read by the CONCATENATE Formula I sent as my last answer.

Hope that makes some sense, let me know if this helps or you need help.

Have a look at the attached file.

[oakie45]

Hi oakie45,

I think I've got what you need using a combination of VBA & Excel Formulas.

I'm using 2 Worksheets, 'Units Available' (Your Worksheet) & 'Unit Numbers', which is the Reference info for ALL your current Units.

NOTE: you can add or remove Units to this list in Column 'A' in the 'Unit Numbers' Worksheet as your list of Units grows or shortens.

How it works - The VBA code searches Column 'C' of the 'Units Available' Worksheet one by one & compare it to Column 'A' of the 'Unit Numbers' Worksheet, the Formula in Column 'C' of the 'Unit Numbers' is then read by the CONCATENATE Formula I sent as my last answer.

Hope that makes some sense, let me know if this helps or you need help.

Have a look at the attached file.

Thank you for all your help. I have been playing around with the file you uploaded and it seemed to be exactly what I needed until this morning. I came across a limitation that I could not figure out. On the Unit Available sheet I need to be able to add more than one unit to a Job under the Unit Numbers Heading. I tried to manipulate the VBA code but had no success. I have very basic knowledge of JAVA coding and can follow what you did but just don't know enough to be able to change what I need.

Please have a look at the attached file and let me know what you think.Unit Job Accounting.xlsm

[noboffinme]

Hi oakie45,

You can have as many Unit Numbers as you like in your 'Reference' list of Units in Column 'A' of the 'Unit Numbers' Worksheet, but it will not recognise a combination of Units.

I've made a couple of changes & added more comments to the code to help you see what I've done.

How it works - 2 parts, what the VBA Macro code does & what the Excel Formula does

VBA Macro Code;

Every time a Change is made (Add data / Delete data etc ...) to the 'Units Available' Worksheet, this code runs

The VBA code searches Column 'C' of the 'Units Available' Worksheet one Value by one & compares it to each value in Column 'A' (Reference List) of the 'Unit Numbers' Worksheet

If a match is found, the Macro pastes that Unit Number into Column 'B' on that Row

Excel Formula;

The Excel Formula in Column 'C' will either Show or Hide the 'Unit Number' depending on the Value in Column 'B'

The CONCATENATE Formula is set to read Column 'C' at all times so will only show Available Unit Numbers

Changing the Code to accommodate multiple Unit Numbers is something I'd need to think about.

You currently have 2000 Rows to add Unit Numbers to - check the code to see where you can change the Rows count to increase it if necc.

Cheers Peter