Hi,

I'm looking for some ideas on how to accomplish a task. I am a contractor and write my project Scope of Works in Excel. The beginning section of my SOW contains all the project calculations that my proposal is based off (ceiling height, floor area, etc.) I've created a spreadsheet that has many 26 columns that covers the 8.5" page, so each column is 2.5 wide. I then merge cells for the actual verbiage of the scope. This has allowed me to place all of my calculations in an organized and compact area because I have multiple columns as to where I can place them. (Ref tab Multi Columns on attached spreadsheet).

This works for formatting my calculations section; however, is a pain in many other areas in the Scope of Work because there are so many merged cells.

I've determined it's worth it to go back to making the columns as wide as they need to be and to eliminate the merged cells. Now I'm looking for a solution as to how to input my Project Calculations section. (Ref tab Min Columns on attached spreadsheet).

And ideal solution is if I could make a "floating table" of some kind that can have many narrow columns and I could place it wherever I want it. I've found ideas such as Edit > Copy Picture where I can place an image where I want it, but this causes other issues.

Any thoughts or ideas as to how to eliminate the need for tons of merged cells, but yet be able to have a section up top that it tight and compact?

Thanks

Attached Files

Floating Table.xlsx (21.0 KB, 15 views)

Merged cells are evil - fact.

Not sure if this is exactly what you are after, but maybe linked Picture is the answer? I have added another sheet called View.

I then copied rows 1 to 19 from Multi Columns, clicked on view sheet, right click paste special > linked picture and then copied rows 19 to 40 from Min Columns clicked on view sheet, right click paste special > linked picture.

The view page then gives you the print view in 1 sheet and as they are linked Pictures then will update, so it might be possible to set this up to be a standard template

Attached Files

Floating Table_Purfleet.xlsx (45.0 KB, 8 views)

You are trying to use your printout form as data entry form - and all your problems start from there.

Instead use data entry part as database. This means you enter data into (preferably defined) Tables following database rules as much as possible. Based on those Tables, you can then design any reports you ever want, and format them in any way.

Added is an example I gobbled together based on your workbook. (There was no way for me to find out what your data columns mostly were meant for, so I used dummy column headers for my attempt.)

You can register any number or projects there. When you select a project in report sheet, the info about selected project, and works and conditions linked with this project are displayed in report sheet.

Attached Files

FloatingTable.xlsx (35.0 KB, 10 views)

gobbled?!


cobbled

OK! I messed up with this one! I tried to express me doing something and not having a clue about it

ArviLaanemets - I think this idea might work for what I need. I could do the same thing, but in some columns off to the side that are hidden. I realized you could do a pic of a table, but didn't realize you could update it.

p45cal - I didn't really follow what you were suggesting. I used to write my Scope of Works in Word and then crunch the numbers in Excel. I then learned that I would occasionally miss something. So now I write the SOW in Excel, and ensure I have some type of associated cost next to each line. (What you guys didn't see is there is a number of columns to the right of SOW that has all my calculaions and take off data in it.) The client doesn't see this info. This process ensures that I covered everything and if something get's changed, the cost and description are right there next to each out.

Based on a further explantion, do you think a database and entry form is a better wayto go?

You always can hide Table columns with formulas, which aren't needed to understand the info in Tables. But I advice:
1. You use defined Tables (Insert>Table);
2. You have all info calculated for Table row in same Table.

This is essential, as;:
1 In defined Tables, when whole Table column contains same formula, then this formula is expanded automatically whenever you add new rows into Table. And formatting works in same way;
Whenever you enter any data immediately below last row of defined Table, the Table expands automatically.
This ensures, that calculated columns have always formulas for every row - so you can hide them, and unless you mess up with formulas somehow, they will always work.
Having any info linked to row of Table stored elsewhere (on same sheet, or on another sheet), you always have to link those tables somehow through some common ID. You can never be sure, that this info simply remains in matching row in future.

Can you work with Excel, or do you need some database app instead, depends on amount of data you'll have, and on complexity of it. When you have a lot of info, excel may be too slow - especially when there are many formulas involved. Usually such Excel apps are for data over certain period - e.g. for year.