yogi_PlaceFormDataInSpecificLocation

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

mjbutterfield said ...
I am trying to set up a way for subcontractors to submit bids via a form and have them populate an estimate.  Here are the couple challenges I am trying to overcome.

Here is a simplified version:

Let's say I have 3 categories- foundation, framing, and sheetrock.  I need to look at the form responses and determine which category the response is supposed to be in.  I then need to place that amount in the spreadsheet for that category.

The second issue is that I would often like to have more than one bid in each category so I need to somehow have the data be placed in the next cell to the right if there is already another bid populated for that category.  I understand a lot of the functions within spreadsheets but could use some advice on the smartest way to accomplish this.  Below is a simplified example of the end product I am looking for:

Category         Vendor 1   Bid 1    Vendor 2  Bid 2     Vendor 3   Bid 3

_______________________________________________________________

Foundation      Joe         1000        Mike        1200

Framing           Jim         2000

Sheetrock        Tim         1000        Bill           900       Frank       1100
--------------------------------------------------------------------------------------------
In Sheet1, I have proposed a solution using the QUERY function ... the resulting layout in Sheet1 does not quite match the layout as requied by mjbutterfield. If the resulting layout in Sheet1 would do, we are all done.

However, to meet mjbutterfield's requirement, I present a solution in Sheet2 where I build up on the work done in Sheet1
Sheet3 however has a more straightforward solution using primarily the FILTER function along with the CONCATENATE and the SPLIT functiuons.
In Sheet4, I provide another QUERY formula based solution which is like the TRANSPOSE of the solution in Sheet1.