Using Excel for Inventory Management

Hi All,

Completely new user here, been driving forklift for several years now and attrition left a vacancy in the front office, so I stepped up. I have a decent grasp of basic Excel functionality, but there's a pickle I'm in and have been trying to spitball a solution to, with little luck. Maybe you can help!

I work for the fulfillment arm of a large marketing company, which means we have quite a few promotional items in and out every day. We recently took over a competitor's client, and needless to say, they're being less than helpful when it comes to providing an accurate pack list for about 40 pallets of goods we just received. I've got a team going through each pallet individually and have a spreadsheet going to at least match the declared number of items to the actual count, and conditional formatting colorizing results so we can at least get an at-a-glance feel of how close we are. Here's the sticking point: Usually we would just use some inventory management software, but all we have relies on Excel, the industry standard. Since it's up to me to get basically 140 pages of word doc into excel format so we can load the webstore, my problem is twofold:

1) Is there any way to use Excel to lump multiple boxes of similar items (we have everything currently sorted by item number, each as its own row) on different pallets without creating a new entry for each item?

and, what I'm really dreading

2) Does anyone have any experience managing inventory with excel? The hope is that we'll get everything in the other software before back orders come in, but there arises the possibility I'm going to have to place and track these pallets in our warehouse while pulling items individually for shipment and keeping my counts accurate.

I'm insanely new at the logistics side of this, any and all help would be much appreciated. I use Excel 2007.