Create Printable Bid from spreadsheet data

[cbird02]

I have a highly customized spreadsheet that I use in the field on my Android Tablet for estimating jobs. It works great. I want to take the next step to being able to print the bid onsite during the appointment. I need advice on what functions I should use and how to structure the overall application. I will refer to 2 sheets: Sheet 1 = Bid calculations, Sheet 2 = Bid output

SHEET 1 - the scopes of work for my bids are different for each job. I have about 10 different measures that we bid. So I have rows for each measure, but each bid has a different combination of measures. So on Sheet 1, I may have bid amounts for Measure 1,4,6,7,9 but not for the other measures. I would like to place a "marker" next top each measure that is to be performed and use that marker to tell SHEET 2 what to import and print.

SHEET 2 - This is the sheet I need to create which I will attempt to make a printable bid. I need to pull data from measures that I mark to be incorporated into the bid. For instance, if I need the bid to print Measure 4,6,7 (with brackets used for imported data from SHEET 1)

SHEET 2

ROW 1 [Measure 4 Description] [Measure 4 Amount]
ROW 2 [Measure 6 Description] [Measure 6 Amount]
ROW 3 [Measure 7 Description] [Measure 7 Amount]
ROW 4 Blank
ROW 5 Blank

​

Now the difficult part is that I don't want a bunch of empty spaces for measures that I am not performing. So, the logic of ROW 1 formula needs to say "import Measure 1 data, unless blank, then import Measure 2 data, unless blank, then import Measure 3 data.....etc AND ROW 2 needs to do the same thing, except if ROW 1 already imported Measure 4 data, it knows to skip to the next specified Measure.

Your help is appreciated.

 

[p45cal]

Record yourself a macro of you auto-filtering the rows on sheet 1 for non-blanks (in an appropriate column, of course), copying the resultant non-blank range to sheet 2. Place that macro here and we'll tweak.

 

[fredlo2008]

Can you provide a sample of your workbook ? Now I think you want a customized report that can be achieved via VBA but I think your table will support it.

Thanks

 

[cbird02]

Can you provide a sample of your workbook ? Now I think you want a customized report that can be achieved via VBA but I think your table will support it.

Thanks

Right now I just have the sheet that data will be pulled from. I have created nothing for the Bid sheet that will pull from the data sheet. Should I enter in a Table from the toolbar or attach a file?

I guess I should mention that my workbook is in Google Sheets. I needed something in the cloud and could not find any good apps supporting native excel which had the managing power of Drive on the cloud. Not really open to any other solutions, so would like to keep the suggestions to how I can do this in Sheets. I just figured I would probably use the same functions, but it sounds like you are swaying me towards VBA.

 

[p45cal]

I guess I should mention that my workbook is in Google Sheets.

Therein lies a problem.. this being an Excel forum, it could be that not many people here will be super familiar with Google Sheets. Surely there's a Google Sheets forum somewhere on the interweb?

 

[cbird02]

Therein lies a problem.. this being an Excel forum, it could be that not many people here will be super familiar with Google Sheets. Surely there's a Google Sheets forum somewhere on the interweb?

I have posted to Sheets forum without much luck. The user base is so much larger for Excel I really want to pull from that experience. I have found that I can tweak Excel functions very easily to Google Sheets. I was assuming there was a way to just use functions, but that is why I am the poster and not the responder .

So, I understand that Google Sheets does not support VBA, but it does support Javascript. Thus, people often just modify their Excel VBA script to work with Google's javascripting. There does seem to be a good amount of support for getting help with this over at the google forums.

I would be grateful for any help with a VBA solution that I can have ported, or if there are other non-VBA suggestions.

 

[p45cal]

but it does support Javascript. Thus, people often just modify their Excel VBA script to work with Google's javascripting.

Well in that case, reproduce your two sheets in Excel, record a macro of what you do, we'll tweak it, then take the resultant code to a Google Sheets forum and ask for it to be translated to javascript. (I'd have a look at this myself, but I'd be totally new to Google sheets and right now have little time to explore.)

 

[cbird02]

Well in that case, reproduce your two sheets in Excel, record a macro of what you do, we'll tweak it, then take the resultant code to a Google Sheets forum and ask for it to be translated to javascript. (I'd have a look at this myself, but I'd be totally new to Google sheets and right now have little time to explore.)

Ok, I recreated in Excel. I don't see an option to upload a file.

 

[p45cal]

I don't think you can, you could upload it to the interweb somewhere and provide a link to it here, but the recorded macro shouldn't be too long, you could cut and paste it here, though it would be useful to see the sheet layouts too.

 

[cbird02]

I have copied my macro below. I also took an image of my spreadsheet, since it seems very important - I will upload to an image server and post a bit later. Please keep in mind this is not a finished spreadsheet. I don't want you all to work real hard on it if I will have to come back to you when I modify the spreadsheet. I am looking for an approach to achieve my goals and will need to tweak the sheet without breaking a script or macro or whatever.

In viewing how the macro recorded, I think the key is that I am marking what goes into each package. There are three packages Saver, Saver Plus, Saver Max. I put marker columns to signify which measures will be on each. I added some zero values to show typical complexity. The Saver Plus package also just adds a measure or two to the Saver package, so that is how it is framed in the sheet.







MACRO
===========================================================
Sub Macro1()
'
' Macro1 Macro
' Import Bid data to Bid Output
'

'
Range("B4:F4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("B5:F5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("B6:F6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("G6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("B7:F7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("G7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[2]"
Range("B8:F8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[2]"
Range("G8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[2]"
Range("B9:F9").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[2]"
Range("G9").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[2]"
Range("B10:F10").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[2]"
Range("G10").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[2]"
Range("G11").Select
ActiveWindow.SmallScroll Down:=6
Range("B18:F18").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-7]C[2]"
Range("G18").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-7]C[2]"
Range("B19:F19").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-7]C[2]"
Range("G19").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-7]C[2]"
Range("G20").Select
ActiveWindow.SmallScroll Down:=9
Range("B25:F25").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-12]C[2]"
Range("G25").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-12]C[2]"
Range("B26:F26").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-12]C[2]"
Range("G26").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-12]C[2]"
Range("B27:F27").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-23]C[2]"
Range("G27").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-23]C[2]"
Range("B28:F28").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[2]"
Range("G28").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[2]"
Range("B29:F29").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[2]"
Range("G29").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[2]"
Range("B30:F30").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[2]"
Range("G30").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[2]"
Range("B31:F31").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[2]"
Range("G31").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[2]"
Range("B32:F32").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[2]"
Range("G32").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[2]"
End Sub

=================================================================