Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
frame style effect required
I'm building a spreadsheet for work, its for quoting a price for
foundations on each house on a building site. i have a front page which has column A detailing all the items required - simplified it would be concrete, stone, bricks etc. Column B is the price/rate for that item then each plot has its own worksheet with the same format except with a quantity to multiply by the rate to get theprice, the prices are then summed and read back to the front page which has a summary for every single plot. the problem i have is that there are 110 worksheets in the book and we're trying to get to a basic template to use for different building sites. the aim is to enter the specific requirements i.e. the bricks concrete etc (which will change from site to site) on the front page and have them update on each worksheet. using a straight =sheet1 a1 type formula has the desired effect except the formating isn't read. we need the titles to be in bold etc and they may not always be in the same cell depending how many items are falling under the heading for that particular site -which rules out applying the format to each worksheet. I've used the camera tool to put an image of the headings on each sheet which updates as they update which works fine aside from the fact the sheet is extremely slow to run. having built a few websites i wondered if it was possible to create something like an i frame where the headings are entered in one sheet which is then embedded at the side of each individual worksheet and remains fixed in place like a frozen pane - basically so that instead of 110 images i'd have a link to another spreadhseet visible. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
frame style effect required
Hi
If you will excuse my impertinence, I can't help but feel that you are tackling this problem in the wrong way. If it were me, I would have just 4 sheets 1 With all the prices for the various items, as you describe. 2 A straightforward Table, with one property to each row, which defined the Name, address etc. and all of the dimensions or quantities for each material to be used. 3 A Quotation sheet, set out with all the formatting that I wanted, with each material listed down the page, and the columns for Quantity and price for each item would be retrieved by Vlookup's of the data from sheet2, for whatever property I had selected from a dropdown list of all properties located at the top of the sheet. 4 A summary sheet with just the property ID and the relevant costs in a single row. Once the property was selected, all relevant cells would be populated and calculated via the formula for viewing or printing. A small macro, would copy the relevant pricing data to Sheet4 for the summary. For help on dropdown lists and using Vlookup, take a look at Debra Dalgleish's site http://www.contextures.com/xlDataVal01.html#Dropdown http://www.contextures.com/xlFunctions02.html If this is of any help and you need more assistance post back -- Regards Roger Govier wrote in message oups.com... I'm building a spreadsheet for work, its for quoting a price for foundations on each house on a building site. i have a front page which has column A detailing all the items required - simplified it would be concrete, stone, bricks etc. Column B is the price/rate for that item then each plot has its own worksheet with the same format except with a quantity to multiply by the rate to get theprice, the prices are then summed and read back to the front page which has a summary for every single plot. the problem i have is that there are 110 worksheets in the book and we're trying to get to a basic template to use for different building sites. the aim is to enter the specific requirements i.e. the bricks concrete etc (which will change from site to site) on the front page and have them update on each worksheet. using a straight =sheet1 a1 type formula has the desired effect except the formating isn't read. we need the titles to be in bold etc and they may not always be in the same cell depending how many items are falling under the heading for that particular site -which rules out applying the format to each worksheet. I've used the camera tool to put an image of the headings on each sheet which updates as they update which works fine aside from the fact the sheet is extremely slow to run. having built a few websites i wondered if it was possible to create something like an i frame where the headings are entered in one sheet which is then embedded at the side of each individual worksheet and remains fixed in place like a frozen pane - basically so that instead of 110 images i'd have a link to another spreadhseet visible. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
frame style effect required
I thought about something similar however each plot requires different
quantites of each material e.g. if the foundation is deeper there will be more concrete. as such when we submit our prices we are asked to supply a quote for individual plots as such we need the sheet to generate both a quote for the whole site and one for each plot. in that respect i suggested having the items down column a and all the plots across the top with a column for quantity and total below them - in practice this would work perfectly well but my boss wants each plot to have its own seperate sheet rather than a massive matrix so unfortuantely i'm stuck with that brief! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
frame style effect required
I thought about something similar however each plot requires different
quantites of each material e.g. if the foundation is deeper there will be more concrete. as such when we submit our prices we are asked to supply a quote for individual plots as such we need the sheet to generate both a quote for the whole site and one for each plot. in that respect i suggested having the items down column a and all the plots across the top with a column for quantity and total below them - in practice this would work perfectly well but my boss wants each plot to have its own seperate sheet rather than a massive matrix so unfortuantely i'm stuck with that brief! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
frame style effect required
I thought about something similar however each plot requires different
quantites of each material e.g. if the foundation is deeper there will be more concrete. as such when we submit our prices we are asked to supply a quote for individual plots as such we need the sheet to generate both a quote for the whole site and one for each plot. in that respect i suggested having the items down column a and all the plots across the top with a column for quantity and total below them - in practice this would work perfectly well but my boss wants each plot to have its own seperate sheet rather than a massive matrix so unfortuantely i'm stuck with that brief! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
frame style effect required
Hi
i suggested having the items down column a and all the plots across the top Yes, but that would be limited data per plot. With my suggestion of one row to each plot, I can't believe there would be more than 255 variables per plot, other than the plot ID, so all detail including length, width and depth of each section of the foundation could be recorded, so the individual data for each would exist on that one row. Your quotation sheet, would provide the fully detailed costing for each individual plot Your Summary sheet would give a single line summary of costs for each plot, and totalled would give the value for the site. Perhaps you should show your boss a small "mock up" first, and then see how he feels. If he doesn't agree, change the boss rather than the specification!!!<vbg -- Regards Roger Govier wrote in message ups.com... I thought about something similar however each plot requires different quantites of each material e.g. if the foundation is deeper there will be more concrete. as such when we submit our prices we are asked to supply a quote for individual plots as such we need the sheet to generate both a quote for the whole site and one for each plot. in that respect i suggested having the items down column a and all the plots across the top with a column for quantity and total below them - in practice this would work perfectly well but my boss wants each plot to have its own seperate sheet rather than a massive matrix so unfortuantely i'm stuck with that brief! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make my borded automatic black instead of pink? | Excel Worksheet Functions | |||
From Dollar ($) sign style to Dirhams (Dhs.) sign style | Excel Discussion (Misc queries) | |||
Can not delete Excel Style | Excel Discussion (Misc queries) | |||
Currency, Percent and Comma Style buttons | Excel Worksheet Functions | |||
changing font style in a complex worksheet function | Excel Worksheet Functions |