VBA to run code in different worksheets and run multiple calculations

J

JimCorb

New Member
Joined
Jan 11, 2016
Messages
6
Dear Friends,
I'm stuck up with interesting but challenging problem and am looking for some guidance on how to fix the problem. Pls allow me try to explain with best possible details.

I have 4 worksheets namely Buildings, Scoring, Costing and Report.

I need to capture user requirements, then match which of my products in raw data matches it. Once I identify it then I've to do Costing calculation and produce the Report as output.

Here's how I'm approching this coding situation.

My BUILDINGS worksheet acts as my raw data and list s different features of all the buildings separately.
Once I capture user requirements then I've to compare it with my raw data in BUILDINGS sheet and shorlist different buildings for my recommendations.
I shortlist this by using SCORING sheet which gives a score of 1 for every matched feature and 0 for unmatched feature against all buildings. Total Score in Row 20 in SCORING sheet helps identify qualified buildings. I've done this part already .For example total score of 8 for a building means it matches the user requirements. Here in my sample the Buildings 1, 6 and 14 are the shortlisted buildings..

These shortlisted buildings need to undergo COSTING sheet one by one.
The COSTING sheet has some embedded formulas from builder which needs some fields from User inputs and then applies the related costs for offering to customer. So my macro needs to run in a loop for Each Qualified Building . For any building, It pastes the user parameters in cells B6 to B8 (shown in GREY cells) . This will result in final costing values to appear in cells B 9 to B14. These Final values along with Building Name needs to be captured in REPORT worksheet.

So if I got THREE buildings as qualified (which matches score of 8) then my output in REPORT worksheet should show all those three buildings along with related costs.

I'm trying to do this in 3-parts.
Part 1 - Identify qualifying buildings. This is done.
Part 2 - Copy only shortlisted buildings along with their features at the bottom of SCORING sheet. Range B25 onwards. This is stuck with errors.
Part -3 Pick up only required parametrs and paste in Costing sheets. This part I need to figuer out best way to setup some Loops.

Problems I'm facing are in:
Part 2 :
Some of my codes run individually when I put them in different sheets and while those sheets are active. In real scenario I've to run them ffrom central module.
But I'm getting errors in referencing to different worksheets through my code in module. I want my code to go to some reference cell for example in Cell A1 in BUILDINGS and then proceed to OFFSET location for copying the required cells. I get this error whenever my code referes to another worksheet within same workbook.
"Run Time error 1004 Activate method of Range Class failed"

Part 3:
What's the best way to pick the required fields from inputs and retrieve the corresponding Costing outputs and paste it in REPORTS ?


Here's my Code:

Option Explicit




Sub ListingQualifiedBuildings()

Dim wb As Workbook
Set wb = ActiveWorkbook

Dim wsBuildings As Worksheet, wsScoring As Worksheet, wsCosting As Worksheet, wsReport As Worksheet

Dim score As String
Dim counter As Long
Dim QualifyingScore As String

Set wsScoring = wb.Worksheets("Scoring")
wsScoring.Range("b20").Activate

'Declare & CORRECT the total qualifying score here


QualifyingScore = 8

Range("B20").Select
counter = 1
Do Until ActiveCell.Value = ""

Range("B20").Select ' required again as each offset is counting from B20

ActiveCell.Offset(0, counter).Select
'Setting limits for counter run
If ActiveCell.Value = "" Then
Exit Sub
Else
End If
score = ActiveCell.Value

If score = QualifyingScore Then

Call RangeSelection

Else
End If
counter = counter + 1

Loop


End Sub








Sub RangeSelection()

Dim wb As Workbook
Set wb = ActiveWorkbook

Dim wsBuildings As Worksheet, wsScoring As Worksheet, wsCosting As Worksheet, wsReport As Worksheet

Dim countColumnLocation As Long

Set wsBuildings = wb.Worksheets("Buildings")
Set wsScoring = wb.Worksheets("Scoring")

'setting temporary counter to know how many columns to offset. It should get column location from qualifying building column in Scoring sheet

countColumnLocation = 2

wsBuildings.Range("a1").Offset(0, countColumnLocation).Activate
wsBuildings.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


'Above copied selection should paste in Scoring sheet at location A25 and in its subsequent Righ columns.
'For example if qualified scores appear in column C,H,J in Scoring Sheet which reflects Building 1, 6 & 14.
'Then actual Building's features for Building 1,6 & 14 will be copied from Range B25,C25 and D25


wsScoring.Range("a25").Activate

Do Until ActiveCell.Value = ""

ActiveCell.Offset(0, 1).Select
Loop


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' End With

End Sub

Excel Screens are here for ref...


Excel 2007
ABCDEFGHI
Misc Building featuresShopsCinemaParty Hall
Misc Building featuresKindergartenShopsShops
Misc Building featuresDance Room
Misc Building featuresYoga
Misc Building featuresBadminton
Misc Building featuresSquash

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D7E4BC"]Building Offer / Criteria[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building1[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building2[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building3[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building4[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building5[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building6[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building…[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building20[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FDE9D9"]Minimum Price (US$)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$2,000[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$3,000[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$4,000[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$5,000[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$1,500[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$2,500[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$3,500[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$4,000[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FDE9D9"]Maximum Price (US$)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$3,800[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$4,800[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$5,800[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$6,800[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$3,300[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$4,300[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$5,300[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$5,800[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FDE9D9"]Currency (USD, GBP, EUR, CNY)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]USD[/TD]
[TD="bgcolor: #FFFFFF, align: center"]GBP[/TD]
[TD="bgcolor: #FFFFFF, align: center"]USD[/TD]
[TD="bgcolor: #FFFFFF, align: center"]EUR[/TD]
[TD="bgcolor: #FFFFFF, align: center"]CNY[/TD]
[TD="bgcolor: #FFFFFF, align: center"]USD[/TD]
[TD="bgcolor: #FFFFFF, align: center"]USD[/TD]
[TD="bgcolor: #FFFFFF, align: center"]USD[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FDE9D9"]Urgency (Lo/Med/Hi/NA)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Lo[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Med[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Med[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Med[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Hi[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Hi[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Med[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Med[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FDE9D9"]Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]High Floor[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Sea Facing[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Private Lawn[/TD]
[TD="bgcolor: #FFFFFF, align: center"]High Floor[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Security[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]Private Lawn[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Sea Facing[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FDE9D9"]Exclusion {Multiple Selections } ( eg..Mountain Facing, No Lift, Security)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Mountain Facing[/TD]
[TD="bgcolor: #FFFFFF, align: center"] No Lift[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Mountain Facing[/TD]
[TD="bgcolor: #FFFFFF, align: center"] No Lift[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Mountain Facing[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Mountain Facing[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Mountain Facing[/TD]
[TD="bgcolor: #FFFFFF, align: center"] No Lift, Security[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FDE9D9"]Sr Citizen Discount (Y/N)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FDE9D9"]Protected Rental (Y/N)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FDE9D9"]Minimum Contract (1,2,3,4 Yrs)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FDE9D9"]Security Deposit (20%, 30%, 40% depending on No. of Contract years)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]20%[/TD]
[TD="bgcolor: #FFFFFF, align: center"]40%[/TD]
[TD="bgcolor: #FFFFFF, align: center"]30%[/TD]
[TD="bgcolor: #FFFFFF, align: center"]20%[/TD]
[TD="bgcolor: #FFFFFF, align: center"]30%[/TD]
[TD="bgcolor: #FFFFFF, align: center"]30%[/TD]
[TD="bgcolor: #FFFFFF, align: center"]30%[/TD]
[TD="bgcolor: #FFFFFF, align: center"]30%[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FDE9D9"]Distance from Mall[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: center"]NA[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: center"]NA[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: center"]NA[/TD]
[TD="bgcolor: #FFFFFF, align: center"]2[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FDE9D9"]Metro station Proximity (Y/N)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FDE9D9"]Bus Stop Proximity[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FDE9D9"]School Proximity[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FDE9D9"]Cinema Proximity (Y/N/NA)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]NA[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]NA[/TD]
[TD="bgcolor: #FFFFFF, align: center"]NA[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FDE9D9"]Hospital (<1 Km, 1-3 KM, >5KM)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1-3 KM[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1 KM[/TD]
[TD="bgcolor: #FFFFFF, align: center"]>5KM[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1 KM[/TD]
[TD="bgcolor: #FFFFFF, align: center"]>5KM[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1-3 KM[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1 KM[/TD]
[TD="bgcolor: #FFFFFF, align: center"]>5KM[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Buildings​



Scoring Sheet

Excel 2007
ABCDEFGHI
Minimum Price (US$)
Maximum Price (US$)
Currency (USD, GBP, EUR, CNY)
Risk Appetite (Lo/Med/Hi/NA)
Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA)
Exclusion {Multiple Selections } ( eg..Mountain Facing, No Lift, Security)
Sr Citizen Discount (Y/N)
Protected Rental (Y/N)
Minimum Contract (1,2,3,4 Yrs)
Security Deposit (20%, 30%, 40% depending on No. of Contract years)
Distance from Mall
Metro Station Proximity (Y/N)
Bus Stop Proximity
School Proximity
Cinema Proximity (Y/N/NA)
Hospital (<1 Km, 1-3 KM, >5KM)
Total

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D8D8D8"]Ref Code[/TD]
[TD="bgcolor: #D8D8D8, align: center"]Building Offer / Criteria[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building1[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building2[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building3[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building4[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building5[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building6[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building…7[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFF00"]Building Offer / Criteria[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building1[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building6[/TD]
[TD="bgcolor: #D7E4BC, align: center"]Building…14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #D8D8D8"]Minimum Price (US$)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$2,000[/TD]
[TD="align: center"]2500[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #D8D8D8"]Maximum Price (US$)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$3,800[/TD]
[TD="align: center"]4300[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #D8D8D8"]Currency (USD, GBP, EUR, CNY)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]USD[/TD]
[TD="align: center"]USD[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #D8D8D8"]Urgency (Lo/Med/Hi/NA)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Lo[/TD]
[TD="align: center"]Hi[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #D8D8D8"]Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]High Floor[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #D8D8D8"]Exclusion {Multiple Selections } ( eg..Mountain Facing, No Lift, Security)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Mountain Facing[/TD]
[TD="align: center"]Mountain Facing[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #D8D8D8"]Sr Citizen Discount (Y/N)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #D8D8D8"]Protected Rental (Y/N)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #D8D8D8"]Minimum Contract (1,2,3,4 Yrs)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #D8D8D8"]Security Deposit (20%, 30%, 40% depending on No. of Contract years)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]20%[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #D8D8D8"]Distance from Mall[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #D8D8D8"]Station Proximity (Y/N)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #D8D8D8"]Bus Stop Proximity[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #D8D8D8"]School Proximity[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #D8D8D8"]Cinema Proximity (Y/N/NA)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]NA[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #D8D8D8"]Hospital (<1 Km, 1-3 KM, >5KM)[/TD]
[TD="bgcolor: #FFFFFF, align: center"]1-3 KM[/TD]
[TD="align: center"]1-3 KM[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #D8D8D8"]Misc Building features[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #D8D8D8"]Misc Building features[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #D8D8D8"]Misc Building features[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="bgcolor: #D8D8D8"]Misc Building features[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="bgcolor: #D8D8D8"]Misc Building features[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]
[TD="bgcolor: #D8D8D8"]Misc Building features[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Scoring​




Costing Sheet

Excel 2007
ABCDE
Minimum Price (US$) (from qualified Building details)
Metro station Proximity (Y/N)
Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA) (from qualified Building details)
Basic Cost {from Builder's Calculation Parameters)Fixed for building in B5
Location Factor (%) {from Builder's Calculation Parameters)Fixed for building location
Cost Overhead % Charges due to Inclusions {from Builder's Calculation Parameters)Provided by builders (% of Basic Cost)
Fixed Maintenance Charges {from Builder's Calculation Parameters)Fixed $ Amt
Total

<tbody>
[TD="align: center"]5[/TD]
[TD="bgcolor: #EEECE1"]Building Name (from qualified Building details)[/TD]
[TD="bgcolor: #D8D8D8"]Building 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #D8D8D8, align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #D8D8D8"]Y[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #D8D8D8"]High Floor[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #CCC0DA, align: right"]5500[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #CCC0DA, align: right"]440[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #CCC0DA, align: right"]68.75[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #CCC0DA, align: right"]550[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #FFFF00, align: right"]6558.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Costing​



Final Report

Excel 2007
ABCD
Buildings NamesBuilding 1Building 6 Building 10
Basic Cost
Location Factor (%)
Cost Overhead % Charges due to Inclusions
Fixed Maintenance Charges
Total

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3500[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]5500[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]280[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]440[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]43.75[/TD]
[TD="align: right"]37.5[/TD]
[TD="align: right"]68.75[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]350[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]550[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #FFFF00, align: right"]4173.75[/TD]
[TD="bgcolor: #FFFF00, align: right"]3577.5[/TD]
[TD="bgcolor: #FFFF00, align: right"]6558.75[/TD]

</tbody>
Report​
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
K
  • Question Question
VBA splitting worksheet to multiple worksheets with formula
Replies
3
Views
580
Marc L
Marc L
M
  • Question Question
Different results running same macro with same data
Replies
3
Views
3K
Logit
Logit
E
  • Question Question
Copy a sheet containing VBA within the same workbook
Replies
1
Views
485
ranman256
ranman256
E
  • Question Question
Combining multiple workbooks into new worksheet with smiliar columns ---VBA code
Replies
0
Views
532
ExcelGeek5038
E

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top