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]

Oh… that code doesn't look like you used auto-filtering as I suggested in msg#2. It's this auto-filtering that takes care of your

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"

Filtering for non-blanks gets rid of them. The following is a macro I recorded when I autofiltered the Sheet1 table on Column A for non-blanks then copied Sheet1 D1:D14 to Sheet3 cell B3 This doesn't copy rows which were blank in column A. I did the same copying from column I of sheet 1 to column G of sheet3:

Sub Macro2()
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("A2:A5").Select
Sheets("Sheet1").Select
Range("D1:D6").Select
Selection.Copy
Range("D1:D14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("I1:I14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

which would then get tweaked to the likes of:

Sub Macro3()
With Sheets("Sheet1")
  .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="<>"
  .Range("D1:D14").Copy Sheets("Sheet3").Range("B2")
  .Range("I1:I14").Copy Sheets("Sheet3").Range("G2")
  .Range("A1").CurrentRegion.AutoFilter
End With
End Sub

This won't work for you because I'm very likely to be choosing the wrong ranges to filter and copy. But if you were to re-record yourself doing this I'd have a better idea. It would be a good idea to clear off any old bid data before any copying goes on.
You picture might help, but the file itself would be better.

 

[cbird02]

 

[p45cal]

I can't see the column letters or row numbers in the first picture.
How's your autofiltereing macro recording coming along?

 

[cbird02]

I can't see the column letters or row numbers in the first picture.
How's your autofiltereing macro recording coming along?

Sorry about that, here is the new snip. I haven't had a chance to record the new macro, I will try this afternoon. I hear you on the autofiltering of blanks, but do you think that will work in my case? Looking at my sheets above, you will notice that it is not when it is blank that it is skipped - sometimes I have a price for everything, but that price is only used in one of three bids (we do good, better, best bids for each bid) I am hoping to use the markers in the first three rows to "tell" the sheet what items to fill in each scenario.

 

[p45cal]

Not complete, but try this:

Sub Macro4()
With Sheets("Sheet1").Range("A1:I16")
  Set dataRng = .Offset(1).Resize(.Rows.Count - 1)
  .AutoFilter Field:=1, Criteria1:="1"
  On Error Resume Next
  dataRng.Columns("D").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
  dataRng.Columns("I").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
  .AutoFilter Field:=1
  .AutoFilter Field:=2, Criteria1:="2"
  dataRng.Columns("D").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B18")
  dataRng.Columns("I").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G18")
  .AutoFilter Field:=2
  .AutoFilter Field:=3, Criteria1:="3"
  dataRng.Columns("D").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B25")
  dataRng.Columns("I").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G25")
  On Error GoTo 0
  .AutoFilter
End With
End Sub

 

[cbird02]

Thanks p45cal - so do I just copy this into a new macro, then run it after clearing my entries in the Data Output sheet?

 

[p45cal]

Yes. If your Data Output sheet is called Data Output then you should change all 6 instances of Sheet3 in the code to Data Output.
Similarly, if your source sheet is not called Sheet1 then you'll want to change the single reference to Sheet1 in the code to whatever you've called that sheet.

 

[cbird02]

Here are the results on Sheet 3 - looks like it is just catching the 3's - so I assume that is since your "offset" is set to 1, then it just gos one column back and therefore doesn't catch columns A and B? Not sure why it only picked up 2 of the "3's". Of course that is the first stab of my engineering oriented but non-programmer mind taking a look at the code.

 

[p45cal]

Is there any way that you could put this file on the interweb somewhere and link to it here.
I fear that you may have some merged cells that are screwing things up.
(bed time here in the uk)

 

[cbird02]

Try this...not sure if share settings are correct for you to download or not, so let me know how it goes.

https://docs.google.com/file/d/0B-_9b7jv6ok0N1MzaFFqZWdkZm8/edit?usp=sharing