Help needed converting part of a spreadsheet into a Word document table please

Hi All,

My Excel price list contains many rows of items and when I want to produce a quote for a customer I end up having to manually fill in a table in my quote Word document which is very time consuming as you can imagine. I am a relative newbie when it comes to Excel and I was wondering whether some kind soul could provide some help please?

I've attached both a snapshot Excel and Word document. What I would like to happen, is for me to enter the Qty and Area details into the spreadsheet and select 'y' or 'n' to include this information into the Word document table. I only would want the Make, Description, Total Cost included in the Word table and a single instance of the Area as per the Word document attached.

Could anyone lend me a hand please and advise whether what I'm trying to achieve is possible?

Many thanks
Daz

Hi Paul,

Thank you ever so much for the reply. That certainly helps a lot. Is there a way to a) only have one instance of the area name and each area being in its own row as in my original Word document example? Lastly, I noticed in your Word file Expr1003. I'm not sure what that is but can it be changed to read Cost or Price?

Best wishes
Daz

Hi Paul,

Most of what you just replied with has gone right over my head I'm afraid and I was fearing this may happen!

Is there another way of producing this?

Many thanks
Daz

That's absolutely incredible Paul. Thanks very much!

One thing I noticed though is that if I have two Area names, which are the same by the way, but in different rows on the spreadsheet, the Word table doesn't group the items under one Area name. I assume this is because the commands and macro are working on a row by row basis. Is that correct and can anything be done? For example, the rows pasted from the spreadsheet below...

ANTH GALLO M1Y SINGLE M1Y NUCLEUS MICRO(1) HAG8 £200.00 1 £200.00 Master Bedroom y

ANTH GALLO ADIVABHG ADIVA L/S SINGLE BK HAGE £250.00 2 £500.00 Main Equipment y

ANTH GALLO ADIVAWHT ADIVA L/S SINGLE WH HAGG £250.00 5 £1,250.00 Master Bedroom y

...Look like this in the Word table:-

Master Bedroom ANTH GALLO - M1Y NUCLEUS MICRO(1) 1 £200.00
Main Equipment ANTH GALLO - ADIVA L/S SINGLE BK 2 £500.00
Master Bedroom ANTH GALLO - ADIVA L/S SINGLE WH 5 £1,250.00

Rather than just the one Area called Master Bedroom containing two rows of data.

Best wishes
Daz

Hi Paul,

I've uploaded the spreadsheet and word document again as it contains (hopefully!), the macro. You can see what I'm getting rather than me pasting the results here. Certainly a lot better but still not grouping the products together.

I guess, I could always sort the Area column A to Z but that puts some rows of data in part for the Word table that I wouldn't want them (such as cables, Installation, etc.)

Best wishes
Daz

I reverted back to the original workbook name and put the 'Cables required for the project' and 'Installation & setup' lines back in and re-run the macro. Looks great apart from one very odd thing. If you look at the v2 sheet I sent you you should see two rows with Bathroom in the Area column. For some reason, in the Word table it lists them like this:-

Bathroom ANTH GALLO - MICRO SINGLE SPK 1 £150.00
Bathroom ANTH GALLO - ADIVA L/S SINGLE SS 1 £275.00

(I padded the second line item with extra spaces as the first row cost and qty format appears to be left justified for some strange reason)

Cheers
Daz

That looks wonderful!

I guess it's important for me to understand how the field codes thing you've done for the Word document works (teach a man to fish yada yada). Are you able or even willing to walk me through it so that I can learn and alter it should I need to?

Also, and I'm a little afraid to ask more of your time but when a quote is prepared I generally don't list the cost of each item, I simply provide a total cost per Area and then a Total project cost at the end of the document. Is it somehow possible to hide / omit the Total Cost column in the Word document and have a total per Area? I've attached an example.

Many thanks
Daz

Simply wonderful! Tiny snag...for some reason it's not including a cost figure for the Cables required for the project. I'm sure that's a simple fix?

That's brilliant!

Going forward are the names of both the excel and word files important and if so, how would I go about editing the field code and macro to ensure it works?

Many thanks
Dazz

Hi. Just returning to this after a few months as I unfortunately suffered a heart attack and my thoughts were elsewhere for obvious reasons. I seem to be getting myself muddled up with all the above changes to the database and macros. Would there be any chance you could put just one file together for me to try again with please?