FileMaker isn't a spreadsheet and won't easily simulate a spreadsheet interface. I suggest focusing on the results you want more than replicating a familiar "look and feel" from a spreadsheet.
What you describe is much like an invoice where a customer purchases a number of items and the cost, tax, etc. for each listed item has to be computed and you won't know how many such items a given customer will buy. You'll just substitute estimate detail lines for line items purchased on an invoice.
What you can do is define a related table of Estimate line items, link this table to your Estimates table by an EstimatesID serial number and then you can place a portal to this table on your Estimates layout. The calculations you describe would be added to the Estimate Line Items table to perform the needed calculations for just one such item. In your Estimate, you can then simply add more records as needed in the portal to generate your different estimates for each quantity.
Check out this simple invoicing demo created by Comment to see how you might put this together: http://fmforums.com/forum/showpost.php?post/309136/
Thank you for the quick response, Phil. I know it's not a spreadsheet and I wasn't trying to replicate Excel exactly, I was just trying to use that to help explain the issue that I'm having.
I can see where keeping each quantity in the Estimate Line Items table would work, but I'm not sure that a portal is going to work to display all of the information that I need. I've attached a sample report format for the information that I need to display for each quantity. Listing all of those fields out horizontally in a portal isn't going to be very functional. Is there a way to change the orientation of a portal so that it lists related records in columns instead of rows?
EDIT: Sorry, couldn't get the screed cap to attach to this reply, so I put it in with the original post.
I think you have your terms mixed up. Portals list data vertically in rows. What you are requesting it so list the data horizontally in columns. Vertically in rows is the easiest way to set this up and this is an example of what I meant by trying not to replicate the way you would do this in a spreadsheet.
What you show, can be done with what is known as the horizontal portal technique, but it's much more complex a design and much less flexible than listing your quote items in rows instead of in columns. Please also note that the number of different quantities can vary, but the rows in your example are fixed. That's where you lose flexibility as you'll be limited to a specific number of columns for your quantities with your layout design.
That said, I can see why you have set it up this way due to the many different items you need to compute for each quantity.
To produce a horizontal portal, you place a portal on your layout defined to show initial row: 1, number of rows: 1. After you have this layout set up exactly like you want it, (You can resize this single row portal to match the first column of data on your sample layout.), duplicate it and then use portal setup to change it to Initial row: 2 ; Number of rows 1. Repeat this 6 more times, and you'll have your horizontal portal.
Ok, I've got you. I think that will work. Thank you very much!
I understand where you're coming from with respect to the portal orientation, and maybe it's just because this is how I've always seen this data reported (Excel or not), but I think I would have a hard time reviewing and mentally organizing all of those details across a row instead of down a column. Maybe that's just a preference thing.