NLC Excel Guru Needed

scottyf · Post by **scottyf** » Mon Jun 04, 2007 3:47 pm

I know, totally off topic, but is there anyone who might be prepared to help me out with an XL project as i'm completely stuck

.Ive had 5 books out from the library on functions, but still aint getting anywhere

I remember a while back somone posted about doing summat in XL, but cant find the thread.

I would even part with some hard earned if someone can offer a suitable solution!

Any help much appreciated as I'm slowly loosing hair follicles!

neil · Post by **neil** » Mon Jun 04, 2007 4:08 pm

I might be able to help you out. Depends on what you're trying to do though?

Rich H · Post by **Rich H** » Mon Jun 04, 2007 4:16 pm

More info required....

scottyf · Post by **scottyf** » Mon Jun 04, 2007 4:46 pm

More info...

Basically I'm trying to create an order and stock control sheet that will be populated from copying and pasteing from an email (a received order). Ideally the order should then be automatically formatted to the correct style (an invoice ready for print) and the stock control sheet automatically updated to show the current stock level. Simple!

The problem is there are a few variables in the emails so that cells might not align on seperate worksheets when the info is pasted in (e.g. someone orders 1 item and someone else orders 5 items - I've no control of the formatting or how many cells the info would cover when pasted in), a function would have to be written to look for specific information and then apply it to a given cell.

It should be possible to automate the entire process through functions and macros!

Ive got the basic formatting done, I just cant get my head round how I can get the raw info from the order email into a fashion that is usable everytime in XL without errors.

If anyone wants my rough worksheet for a gander feel free to PM me.

My heads mushed

. I'll be amazed if anyone can comprehend the above!

robin · Post by **robin** » Mon Jun 04, 2007 6:37 pm

If I were you, I wouldn't start from there ....

There are better ways of doing this, even if you are restricted to receiving your orders by email in this badly formatted way.

Cheers,
Robin

scottyf · Post by **scottyf** » Mon Jun 04, 2007 6:43 pm

robin wrote:If I were you, I wouldn't start from there ....

There are better ways of doing this, even if you are restricted to receiving your orders by email in this badly formatted way.

Cheers,
Robin

If your talking about going the database route, that would be ideal, but this really is just a stop gap solution for a wee while. Unless you have another solution?

robin · Post by **robin** » Mon Jun 04, 2007 8:32 pm

Well, you're making it harder than it needs to be and I would suggest investing your efforts in something like Filemaker Pro or (vomit) MS Access rather than trying to do it in Excel; sure that's a "database", but it's not any more complicated than an every growing spreadsheet - it's just a file on the disk and an application that opens it ...

[I would obviously write it in assembler and store the results on a 8" floppy disk, being a dinosaur;-)]

Cheers,
Robin

Skyenet · Post by **Skyenet** » Mon Jun 04, 2007 9:27 pm

robin wrote: I would suggest investing your efforts in something like Filemaker Pro

I set up applications in FilemakerPro including graphics from Apple Quicktake cameras long before Access came out. I loved using it. When Access came out it wasn't nearly as powerful or as easy to use. Even the later versions were missing standard FilemakerPro Features. Its amazing how much you find out about a program when you have real life jobs to do.

ninja · Post by **ninja** » Mon Jun 04, 2007 10:44 pm

you can copy and past word info into a spreadsheet fairly easily mate. there's a format option for the pasted info. can you email me an example of what you're looking to paste and the preferred format and i'll take a look?

chris.penman@wyg.com

cheers

ninja

ninja · Post by **ninja** » Mon Jun 04, 2007 10:45 pm

obviously i'll talk you through how to do it once i've sussed it out!

ninja

scottyf · Post by **scottyf** » Mon Jun 04, 2007 11:21 pm

robin wrote:Well, you're making it harder than it needs to be and I would suggest investing your efforts in something like Filemaker Pro or (vomit) MS Access rather than trying to do it in Excel; sure that's a "database", but it's not any more complicated than an every growing spreadsheet - it's just a file on the disk and an application that opens it ...

[I would obviously write it in assembler and store the results on a 8" floppy disk, being a dinosaur;-)]

Cheers,
Robin

Ta Robin, I totally overlooked FileMaker, I used it briefly many moons ago. Ill see if I can dig up a copy from somewhere. Ill know doubt be back scratching my head again.

scottyf · Post by **scottyf** » Tue Jun 05, 2007 9:51 am

ninja wrote:you can copy and past word info into a spreadsheet fairly easily mate. there's a format option for the pasted info. can you email me an example of what you're looking to paste and the preferred format and i'll take a look?

chris.penman@wyg.com

cheers

ninja

Cheers for the offer matey,
I'm not having a prob with the copying and pasteing, I already have a macro which duplicates the worksheet, removes the original HTML formatting and pastes the raw data. The problem comes when writing functions to examine the raw data. i.e look for word="Order Quantity" within this specific cell range then copy the vital info (ignoring extraneous info) to this cell range... Ive written quite a few functions before but I just cant fathom how to break it down logically.

I'm going to examine Robin's route of using file maker instead of using spreadsheets. It makes more sense in the long run.

Cheers

Scott F

scottyf · Post by **scottyf** » Thu Jun 07, 2007 1:44 pm

neil wrote:I might be able to help you out. Depends on what you're trying to do though?

I just want to say a big thanks to Neil who it seems is some kind of XL genius!!!

Cracking work so far mate, many thanks.