Hai smart peepul of NC4x4 (data management, MS Excel related)

jeepinmatt

#1 WEBWHEELER
Moderator
Joined
Mar 24, 2005
Location
Stanley, NC
I need to create a quote log for work and for the sake of it actually continuing to be updated and useful it needs to be fairly automated. The way everything is setup, we have about 6 people putting quotes into a common folder with a common filename format based on date. The quotes themselves are also the same Excel format, generated from the same online catalog. There are a few fields that change, but overall it is consistent enough to deal with the exceptions manually. Buying software is not really an option, unless it's really cheap. So I've been playing around with Excel Power Query. And it works for getting some of the data, but not all of it. The biggest area where I am struggling is the total quantity of items and total price, because it varies depending on the number of line items. And there is additional data below the line items, so I can't just make an bunch of repetitive lines to run out past the end. So am I even on the right track? Is Power Query a waste of time? I've had trouble getting it to update, and by "had trouble" I mean it hasn't updated the data since I generated and formatted the first query.

Attached is a sample of the quite and it could have any where from 1 to about 30 line items.
 

Attachments

  • Sample Quote.pdf
    14.8 KB · Views: 207
I was thinking the same as ^. Have an access database with excel file upload/append to a master table or forms with input fields. You could setup error alerts to catch any incorrect entries in the uploads or form.
 
I forgot to mention that I hate Access databases. I'd really like to avoid that route if possible. This is for multiple people to access and edit, so I want to keep it as simple as possible.
 
Though you say you hate access, my wife built an access database for me to keep up with work orders and to write invoices. It works well. I hate computers and even I can use it. She also uses a access database for multiple people to input production problems at her day job. I have set forms in mine that all I do is fill in blanks and it will generate my invoices. But, if you hate it, you hate it. I can understand that.
 
Back
Top