TheAssetMgr
Board Regular
- Joined
- Nov 8, 2011
- Messages
- 63
My organization responds to international disaster scenes and my team needs to be able to generate a report to send to the Logistics team which shows everything we’re palletizing and sending out to the aircraft. We have standardized kits with homogenized sets of equipment and we have an itemized list in Excel with what each kit contains.
To prevent the hand jamming errors that happen I’d like to be able to remove the human copy/paste element by presenting the user with an interface of some kind that allows them to select at the kit level what’s being sent and how many are being sent. Then the contents of the kits, multiplied by the quantity of kits, should be pulled in from elsewhere in the spreadsheet and arranged in a specific format for exporting to the Logistics team.
For example, let’s say I want to send three laptop kits and a tool kit out. The laptop kit contains 6 laptops and various accessories and the columns I have in Excel are Description, Units, Cost, and Total Cost. The tool kit is just a pile of tools and like the laptop kit, each tool has a line item with each of the listed columns.
I’m not even sure how I’d want to be presented with the option to select the type and quantity of kits I send out but I’d need to be able to make as many kit selections as I have kit types, which is about 25 or so. I want to be able to click something to say I’m sending three laptops kits and one tool kit and the report needs to include a section for the laptop kits that multiples all the standard quantities and prices by three to give me a chart that shows what’s going out.
Is there a built in feature (PowerQuery?) to help with this sort of thing or am I just looking at a lot of VBA scripting? Maybe using a bunch of vlookups would do the trick? Is Excel even the right tool for this sort of thing?
Hoping one of you gurus can help me fill in my knowledge gaps. I can add screenshots of what I’d like the end results to look like if that would help.
Thanks!
To prevent the hand jamming errors that happen I’d like to be able to remove the human copy/paste element by presenting the user with an interface of some kind that allows them to select at the kit level what’s being sent and how many are being sent. Then the contents of the kits, multiplied by the quantity of kits, should be pulled in from elsewhere in the spreadsheet and arranged in a specific format for exporting to the Logistics team.
For example, let’s say I want to send three laptop kits and a tool kit out. The laptop kit contains 6 laptops and various accessories and the columns I have in Excel are Description, Units, Cost, and Total Cost. The tool kit is just a pile of tools and like the laptop kit, each tool has a line item with each of the listed columns.
I’m not even sure how I’d want to be presented with the option to select the type and quantity of kits I send out but I’d need to be able to make as many kit selections as I have kit types, which is about 25 or so. I want to be able to click something to say I’m sending three laptops kits and one tool kit and the report needs to include a section for the laptop kits that multiples all the standard quantities and prices by three to give me a chart that shows what’s going out.
Is there a built in feature (PowerQuery?) to help with this sort of thing or am I just looking at a lot of VBA scripting? Maybe using a bunch of vlookups would do the trick? Is Excel even the right tool for this sort of thing?
Hoping one of you gurus can help me fill in my knowledge gaps. I can add screenshots of what I’d like the end results to look like if that would help.
Thanks!
