frame style effect required

[themastertaylor]

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.

 

[Roger Govier]

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

 

[themastertaylor]

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!

 

[themastertaylor]

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!

 

[themastertaylor]

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!

 

[Roger Govier]

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>