Results 1 to 10 of 10

Thread: Is MS Excel the right App for this?

  1. #1
    Senior Member
    Join Date
    Feb 2006
    Posts
    1,764
    Thanks
    101
    Thanked
    74 times in 67 posts
    • pp05's system
      • Motherboard:
      • AsRock Fatal1ty B450 Gaming itx
      • CPU:
      • Ryzen 3 2200G
      • Memory:
      • Ballistix Elite 8GB Kit 3200 UDIMM
      • Storage:
      • Kingston 240gb SSD
      • PSU:
      • Kolink SFX 350W PSU
      • Case:
      • Kolink Sattelite plus MITX
      • Operating System:
      • Windows 10

    Is MS Excel the right App for this?

    We currently use excel for basic shipping information in a list form.

    Presently the system works as follows. Clients are emailed orders, they fax this to my friend, who then gets someone to re-enter it into pc as the office has no system atm. Part of reluctance is end-user has a lot on their plate, no computer training and sees it all as complicated. This means sometimes there are errors which only get seen late. Also hours of tedious data entry.

    What I want to know is:

    Can we automate the above into following with Excel?

    If a client emails friend the order(in a table layout) assuming the template is same, can this then be scanned in some way taking the data out and reorganising it in list form in a seperate Master workbook called Orders.

    When he completes an item within the order, he merely type the date it is shipped. Can he through a seprate sheet called shipping, scan the master workbook for all items by date column, and having only those items copied over to shipping sheet ready for email back to client. There isn't any addition, basic date entry.

  2. #2
    Senior Member
    Join Date
    Jul 2004
    Location
    London
    Posts
    2,456
    Thanks
    100
    Thanked
    75 times in 51 posts
    • Mblaster's system
      • Motherboard:
      • ASUS PK5 Premium
      • CPU:
      • Intel i5 2500K
      • Memory:
      • 8gb DDR3
      • Storage:
      • Intel X25 SSD + WD 2TB HDD
      • Graphics card(s):
      • Nvidia GeForce GTX 570
      • PSU:
      • Corsair HX520
      • Case:
      • Antec P180
      • Operating System:
      • Windows 7 Professional x64
      • Monitor(s):
      • HP w2207 (22" wide)
      • Internet:
      • Rubbish ADSL

    Re: Is MS Excel the right App for this?

    Quote Originally Posted by pp05 View Post
    We currently use excel for basic shipping information in a list form.

    Presently the system works as follows. Clients are emailed orders, they fax this to my friend, who then gets someone to re-enter it into pc as the office has no system atm. Part of reluctance is end-user has a lot on their plate, no computer training and sees it all as complicated. This means sometimes there are errors which only get seen late. Also hours of tedious data entry.

    What I want to know is:

    Can we automate the above into following with Excel?

    If a client emails friend the order(in a table layout) assuming the template is same, can this then be scanned in some way taking the data out and reorganising it in list form in a seperate Master workbook called Orders.
    If the client sends the order in a CSV file as an attachment to the email then it could be fairly simply imported the spreadsheet without having to type it all out. But if you want to do an import from the body of the email then that's much more complicated. Also, doing this means you are relying on your customers to have the data formatted exactly how you need it, since there's no validation on this import, so I would not recommend importing data directly from the client. I don't think they'd like to hear that their order was wrong because they didn't send their data over in the correct format.

    Quote Originally Posted by pp05 View Post
    When he completes an item within the order, he merely type the date it is shipped. Can he through a seprate sheet called shipping, scan the master workbook for all items by date column, and having only those items copied over to shipping sheet ready for email back to client. There isn't any addition, basic date entry.
    Don't really understand what you're asking here, do you want a second sheet to pull all orders from the first sheet that have the specified shipping date? Or all the orders with an entry for shipping date? And would you want to filter on the client name as well? All those things are possible if you have the data (e.g. the date column would need to be set up in Excel as a proper date field, rather than a text field with the date written in inconsistent formats).

    You can do most of what you're asking (if I've understood it correctly) in Excel, but it sounds to me like you would be better off using a database (such as MS Access) rather than a spreadsheet to run this. That would allow you to use a form to enter the data (much easier on the eyes for data entry, and you can have automatic validation to check data as it is entered), and you could use reports to automatically get information out of the database based on pre-set rules and have it formatted for print/email.

    So basically, no to the automatic import from the email, and yes to the rest, but it'd probably be better in the long run to use Access if you want to put in the time initially to get it set up and get people trained.
    I don't mean to sound cold, or cruel, or vicious, but I am so that's the way it comes out.

  3. #3
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Gateshead
    Posts
    15,196
    Thanks
    1,230
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: Is MS Excel the right App for this?

    Quote Originally Posted by pp05 View Post
    ... they fax this to my friend ...
    Frankly, here's your main problem straight away Fax? Tell your client it's the 21st Century!


    OK, now I've got that off my chest: what you want to do can almost certainly be done in Excel, but it won't be the easiest way. Doing it in Access instead will alkmost certainly be easier, and work better. So, if you've got Access, find someone who can use Access properly and get them to set this up for you.

    If you don't have Access, then it'll be trickier to do, but it's certainly possible. The first question is does your client have Excel? If they do, you can create an order template in Excel for them to fill out which can then be linked into a master Excel spreadsheet, and you can use a variety of methods to pull the data in.

    If they don't have Excel, pretty much anything else you try to do is going to be more complicated and long winded than just using a manual system, frankly...

  4. #4
    Senior Member
    Join Date
    Feb 2006
    Posts
    1,764
    Thanks
    101
    Thanked
    74 times in 67 posts
    • pp05's system
      • Motherboard:
      • AsRock Fatal1ty B450 Gaming itx
      • CPU:
      • Ryzen 3 2200G
      • Memory:
      • Ballistix Elite 8GB Kit 3200 UDIMM
      • Storage:
      • Kingston 240gb SSD
      • PSU:
      • Kolink SFX 350W PSU
      • Case:
      • Kolink Sattelite plus MITX
      • Operating System:
      • Windows 10

    Re: Is MS Excel the right App for this?

    Quote Originally Posted by Mblaster View Post
    1. If the client sends the order in a CSV file as an attachment to the email then it could be fairly simply imported the spreadsheet without having to type it all out. But if you want to do an import from the body of the email then that's much more complicated. Also, doing this means you are relying on your customers to have the data formatted exactly how you need it, since there's no validation on this import, so I would not recommend importing data directly from the client. I don't think they'd like to hear that their order was wrong because they didn't send their data over in the correct format.

    ***
    I agree.

    The client usually faxes. When their fax was down they sent it as an excel spreedsheet attached to email in .xls format to be opened in Excel on friends system and not within body of the email itself.

    ***

    Don't really understand what you're asking here, do you want a second sheet to pull all orders from the first sheet that have the specified shipping date? Or all the orders with an entry for shipping date? And would you want to filter on the client name as well? All those things are possible if you have the data (e.g. the date column would need to be set up in Excel as a proper date field, rather than a text field with the date written in inconsistent formats).

    ***
    Yes the former.

    What I'm trying to do is limit my friends data entry into the system to date only in master workbook.

    That of items despatched which he will email client a bit later in the day after he has made a second sheet (presently) and copy and pasted individual items over.

    So rather than him copying and pasting the individual items with '15th feb' date next to them, if there can be some function that presents him a dialog box for 'date', he enters 15th feb or 15/2, and it scans the date column of the 1st sheet based on that argument for matching items and inputs it into this new sheet automatically.

    ***

    You can do most of what you're asking (if I've understood it correctly) in Excel, but it sounds to me like you would be better off using a database (such as MS Access) rather than a spreadsheet to run this. That would allow you to use a form to enter the data (much easier on the eyes for data entry, and you can have automatic validation to check data as it is entered), and you could use reports to automatically get information out of the database based on pre-set rules and have it formatted for print/email.

    So basically, no to the automatic import from the email, and yes to the rest, but it'd probably be better in the long run to use Access if you want to put in the time initially to get it set up and get people trained.
    This is the reason why I have kept it Excel. If it looks too complicated they don't want to know.

  5. #5
    Senior Member
    Join Date
    Feb 2006
    Posts
    1,764
    Thanks
    101
    Thanked
    74 times in 67 posts
    • pp05's system
      • Motherboard:
      • AsRock Fatal1ty B450 Gaming itx
      • CPU:
      • Ryzen 3 2200G
      • Memory:
      • Ballistix Elite 8GB Kit 3200 UDIMM
      • Storage:
      • Kingston 240gb SSD
      • PSU:
      • Kolink SFX 350W PSU
      • Case:
      • Kolink Sattelite plus MITX
      • Operating System:
      • Windows 10

    Re: Is MS Excel the right App for this?

    Jim tell me about it. They have been working by pen and paper for the last decade. I have to bear that in mind when suggesting anything.

    But they like TomTom, so something that can be presented in a click or two is best way to move them towards email and maybe opening them towards a more robust and sophisticated solution later.

    Quote Originally Posted by scaryjim View Post
    Frankly, here's your main problem straight away Fax? Tell your client it's the 21st Century!


    OK, now I've got that off my chest: what you want to do can almost certainly be done in Excel, but it won't be the easiest way. Doing it in Access instead will alkmost certainly be easier, and work better. So, if you've got Access, find someone who can use Access properly and get them to set this up for you.

    If you don't have Access, then it'll be trickier to do, but it's certainly possible. The first question is does your client have Excel? If they do, you can create an order template in Excel for them to fill out which can then be linked into a master Excel spreadsheet, and you can use a variety of methods to pull the data in.

    If they don't have Excel, pretty much anything else you try to do is going to be more complicated and long winded than just using a manual system, frankly...
    How much do you think a developer may charge given it's not hugely complex system.

    They have excel and open office. They haven't got Access yet.

  6. #6
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: Is MS Excel the right App for this?

    Access is dead easy. Start playing around yourself and you'll be up and running in no time.

  7. #7
    Jay
    Jay is offline
    Gentlemen.. we're history Jay's Avatar
    Join Date
    Aug 2006
    Location
    Jita
    Posts
    8,365
    Thanks
    304
    Thanked
    568 times in 409 posts

    Re: Is MS Excel the right App for this?

    a web based app and SQL would be really good for this kind of thing, then its all automated.
    □ΞVΞ□

  8. #8
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Gateshead
    Posts
    15,196
    Thanks
    1,230
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: Is MS Excel the right App for this?

    Quote Originally Posted by pp05 View Post
    How much do you think a developer may charge given it's not hugely complex system.

    They have excel and open office. They haven't got Access yet.
    Quote Originally Posted by Jay View Post
    a web based app and SQL would be really good for this kind of thing, then its all automated.
    Jay's quite right, a web app where they can enter their orders would be ideal, but that's a step or two of complexity ahead, in terms of setup, and probably cost. If they don't have Access, you're looking at doing the programming in Excel which is a bit more complex (Excel isn't really that well set up for data imports and handling). However, it's far from impossible.

    How much a developer will charge you will depend entirely on the developer, and most (IMNSHO) overprice their work massively. For an Excel-based system, you'd probably be looking at half a day's consultancy to specify the exact requirements, and maybe 2 days programming to implement it: a reaonable devleoper would charge between £500 and £1000; but if you go looking for quotes don't be surprised if some come back nearer £5k: a lot of consultants and developers tend to charge as much as they think the client will pay, rather than as much as it will cost them to do the work.

    A web based system would probably be more expensive, because you'd need to fit a lot more functionality into it, but it would also allow you to go for something much more flexible, and it could (if it's well implemented) automate the vast majority of your processes. Expect to pay at least £3000 for this, but expect to get a lot more for your money as well.

    I do Office and Web development: if you're interested in getting a quote off me PM me and I'll send you my business contact details.

  9. #9
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Is MS Excel the right App for this?

    Ok, first off I'm one of those who jim would say over charges, I also am fully booked for the next 7 months, so evidently I'm not charging enough.

    In my time there is so much **** I've seen pass off as a system. Obviously the place I'm at now is just milk and honey.

    I would say, do you need a web system for this? I've yet to read anything that says yes.

    Quick pro's n con's for web systems.
    Web systems are normally the cheapest to pay for developer time per hour.
    They are often the simplist to roll out new updates (ie update one server).
    They can often be accessed from multiple places with ease.
    You can sometimes use a fully managed service (ie they have a server you access via the web).
    ---- end of pro's ----
    They are on the whole the worst for maintance.
    They normally have the least amount of user satisfaction (see how many people download the TFL tube status iPhone app, rather than just use the web browser).
    They have integration issues on the client (all is fine and well when your just having a web page, but when you want to do anything advanced with the clipboard, like read the different data streams, your into active-x or similar land(NOT a nice place!)).
    They often take a long time to implement compared to access.

    The advantage of going for something like Access is that you may well find you have a worker who is able to really use and customise it without on going costs of a developer.

    Not to mention its less bespoke. All web devs have their fav technologies and no other come close, they also generally seam to suffer from "My hammer is best for all tasks".

    As such, I'd have a good think about the long term goals of the app. What skills you have in house and how you can align the project with those. Also ensure you don't get straddled with something bespoke that your dependant on. Whilst crack dealers give the first hit away for free, developers have learnt they can charge money for it
    throw new ArgumentException (String, String, Exception)

  10. #10
    Senior Member
    Join Date
    Feb 2006
    Posts
    1,764
    Thanks
    101
    Thanked
    74 times in 67 posts
    • pp05's system
      • Motherboard:
      • AsRock Fatal1ty B450 Gaming itx
      • CPU:
      • Ryzen 3 2200G
      • Memory:
      • Ballistix Elite 8GB Kit 3200 UDIMM
      • Storage:
      • Kingston 240gb SSD
      • PSU:
      • Kolink SFX 350W PSU
      • Case:
      • Kolink Sattelite plus MITX
      • Operating System:
      • Windows 10

    Re: Is MS Excel the right App for this?

    Animus I think they are looking for something temporary now that will save a bit of time until they are confident they can use it. Web solutions sound alright but that add an extra layer.

    The firm has 1 member of staff who has done some computer course but also has other responsibilities. They know how to use word/office and web as a normal user.

    I'm just thinking if we remove the 'scanning of original orders from client' function from list i.e. they can input this into excel manually according to their own template.

    Then that would leave 1 workbook with data, we just need excel to search within it for specific date e.g. on column F of every sheet and output those to the screen to be viewed/printed off.

    For example: All orders shipped in february display them within same sheet etc. Could this be done without something complex, through one of the in-built functions like search? or does it need a bit of vba programming?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Vlookup not working (sort of)
    By piggeh in forum Help! Quick Relief From Tech Headaches
    Replies: 9
    Last Post: 03-09-2010, 04:35 PM
  2. Finding new data from 2 excel files?
    By menthel in forum Software
    Replies: 0
    Last Post: 03-04-2008, 09:51 AM
  3. .NET Reading Multiple Excel Files
    By Shadow_101 in forum Software
    Replies: 5
    Last Post: 23-11-2007, 03:22 PM
  4. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  5. Replies: 6
    Last Post: 12-08-2005, 08:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •