Convert to macros

[MARKEGANDERSON]

Hello All:

I have the current formula:

=SUMPRODUCT(--(Adhoc!$C$2:$C$15000=$B5),--(Adhoc!$F$2:$F$15000="Government Owned (Unit or Buyout)"),--(Adhoc!$E$2:$E$15000="20FT-DRY")+(Adhoc!$E$2:$E$15000="20FT-DRYAMMO")+(Adhoc!$E$2:$E$15000="20FT-DRYHICUBE"))

it works just fine, but the problem is having 2448 cells that requires similar formulas, which makes my workbook 10mb.

Can someone convert this into Macros?

I just need the basic set up, and I will piggyback with what you provide.

Thanks

 

[RichardMGreen]

How is your data laid out?
And where are you putting the results?

 

[MARKEGANDERSON]

Tab "LOGCAP III" is my results tab:

Cell C5=SUMPRODUCT(--(Adhoc!$C$2:$C$15000=$B5),--(Adhoc!$F$2:$F$15000="Government Owned (Unit or Buyout)"),--(Adhoc!$E$2:$E$15000="20FT-DRY")+(Adhoc!$E$2:$E$15000="20FT-DRYAMMO")+(Adhoc!$E$2:$E$15000="20FT-DRYHICUBE"))

Cell D5 =SUMPRODUCT(--(Adhoc!$C$2:$C$15000=$B5),--(Adhoc!$F$2:$F$15000="Government Owned (Unit or Buyout)"),--(Adhoc!$E$2:$E$15000="20FT-REF")+(Adhoc!$E$2:$E$15000="20FT-REFHICUBE"))

Tab "Adhoc" is my sheet i search for the required data:

Column C is the location Name (same as above Adhoc!$C$2:$C$15000=$B5)

Column E is the Container Size =()"),--(Adhoc!$E$2:$E$15000="20FT-DRY")+(Adhoc!$E$2:$E$15000="20FT-DRYAMMO")+(Adhoc!$E$2:$E$15000="20FT-DRYHICUBE"))

Column f is the Owner (Adhoc!$F$2:$F$15000="Government Owned (Unit or Buyout)")

 

[RichardMGreen]

Can you post some example data along with the sheet name it's come from?
I think I've got the right idea but I want to make sure.

 

[MARKEGANDERSON]

How can I paste as a picture?

 

[RichardMGreen]

Have a look for the HTML-maker in one of the stickies.
That'll do everything you need.

 

[MARKEGANDERSON]

Results Tab "LOGCAP II"
(B) (C)
20 " Dry 40" Dry
Cell B5 Cell C5

Data tab "Adhoc" Tab

(c) (d)
Location Container Size
Cell C7 Cell D7


This is the best i can do sir...

cant dind that sticky

 

[RichardMGreen]

You could convert this to a macro, but it would take quite some time to calculate.
Converting formulas to macros is a trade off, a smaller file but a longer calculation time.

Let me have a check on the best way to do this and I'll get back to you.

 

[MARKEGANDERSON]

If that is the case (trade off). I will stick to my formulas. I thought using the macros would give me a smaller and quicker calculation.

Thanks again.