Version: Excel 2016
Hello members. As you can see I am very new to this forum. I have used other Microsoft forums for Access mostly, but this is my first Excel one. I hope to contribute a lot to this forum in any way I can.
Now, on to the scenario. I have a large file with about 430 columns and 1100 rows of data. The reason for so many columns is that each one apart from the first 8 are dates. The rows are stock items. Most of the rows are duplicates though, since they go by batch number (expiry date). (This stock is all consumer goods such as biscuits, milk etc.)
So this file is a way to track how many of each consumer good we sent to a certain customer and on which date.
I have made a copy and uploaded this file to my OneDrive storage which comes with Office 365 subscription. Now, via Excel Online I can share this file with certain customers sending them invites via Email. These customers will be able to see what items are in stock and they can place orders based on this, since they currently have to request what we have in stock. This takes too much time.
The final step is that I'm going to embed the workbook in a page on our company website and allow only access to these certain customers.
Now, this is all working well.
The issue:
I want to create a User Form order system which will allow the customer to see what we have in stock via a multi-select ListBox. The ListBox must show the "BatchNumber (Column 1);Description(Column 2);Quantity (Column 422)" columns for each item. However, the "Quantity" column is filtered to remove all values with "0". So the ListBox must only show the "BatchNumber;Description;Quantity" where "Quantity" is not "0".
This way the user can easily see what items are in stock and the quantity. Once they have chosen a few items, they should be able to press a "ORDER" button and a report (can Excel make reports?) must be generated from the items they chose and the quantities.
I have already tried using data validation for the stock sheet. It works well, but the list dropdown shows ALL the values instead of only the filtered ones where the "Quantity" is not "0". This would be so easy to use and I can create the final product from this, however I can't find a way for the data validation list to only show the filtered values. (Where the "Quantity" is not "0")
I tried using the VBA editor, since I have some experience with Access VBA, but I'm struggling with it.
I hope it's not too much to ask and I hope it was clear what the requirements are.
Thanks for reading and I appreciate any and all help!
Hello members. As you can see I am very new to this forum. I have used other Microsoft forums for Access mostly, but this is my first Excel one. I hope to contribute a lot to this forum in any way I can.
Now, on to the scenario. I have a large file with about 430 columns and 1100 rows of data. The reason for so many columns is that each one apart from the first 8 are dates. The rows are stock items. Most of the rows are duplicates though, since they go by batch number (expiry date). (This stock is all consumer goods such as biscuits, milk etc.)
So this file is a way to track how many of each consumer good we sent to a certain customer and on which date.
I have made a copy and uploaded this file to my OneDrive storage which comes with Office 365 subscription. Now, via Excel Online I can share this file with certain customers sending them invites via Email. These customers will be able to see what items are in stock and they can place orders based on this, since they currently have to request what we have in stock. This takes too much time.
The final step is that I'm going to embed the workbook in a page on our company website and allow only access to these certain customers.
Now, this is all working well.
The issue:
I want to create a User Form order system which will allow the customer to see what we have in stock via a multi-select ListBox. The ListBox must show the "BatchNumber (Column 1);Description(Column 2);Quantity (Column 422)" columns for each item. However, the "Quantity" column is filtered to remove all values with "0". So the ListBox must only show the "BatchNumber;Description;Quantity" where "Quantity" is not "0".
This way the user can easily see what items are in stock and the quantity. Once they have chosen a few items, they should be able to press a "ORDER" button and a report (can Excel make reports?) must be generated from the items they chose and the quantities.
I have already tried using data validation for the stock sheet. It works well, but the list dropdown shows ALL the values instead of only the filtered ones where the "Quantity" is not "0". This would be so easy to use and I can create the final product from this, however I can't find a way for the data validation list to only show the filtered values. (Where the "Quantity" is not "0")
I tried using the VBA editor, since I have some experience with Access VBA, but I'm struggling with it.
I hope it's not too much to ask and I hope it was clear what the requirements are.
Thanks for reading and I appreciate any and all help!
