Replies

Hi Maud,

Are you still looking at this? If not, no problem; will make what I have work.

Thanks
Meleia

Hi Maud,

Anything new on this?

Thanks
Meleia

Hi Maud,

Have run into a couple of problems. When some of the % of Cost per Site totals are changed, the values of that site don’t change. For example (using sheet above), if I change the value in Y43 (the total row), the values in Y3:Y42 should change, and Y3:Y42 should equal the new number typed into the Total row, Y43. This does not happen in all of the instances.

Also, if I make changes in the % of Cost per Site in Business B, the values in Business D change — this is correct. However the Total in Business D doesn’t change to reflect the new Total.
And, If Units per Site are changed in Business E, the corresponding change does not happen in Business D as it should. I can change these values to reflect Business D = Business E, if it doesn’t interfere with anything else.

And lastly, just a question. When I highlight a total, for example Y43 above, the data for the NEXT site highlights. What is the purpose of this?

Any help appreciated, as always.
Meleia

Maud,

All I can say is WOW! Can’t believe the work you put into this. Believe it will be exactly what I need. I’ll be starting to load data into the sheet in the next week and will let you know if there are any tweaks. But it looks perfect! I can’t thank you enough. And again, the level of work and detail is fantastic.

The results from you and Zeddy will both be put to use; Zeddy’s range/formula solution on another project.
All of your efforts are much appreciated.

MM

Hi Maud,

Hoping that a full-on VBA solution is still on your radar! Just checking before I begin to set up a formula/range sheet.

Thanks
MM

Hi Zeddy,
Thanks for the solution above. Have been in the middle of another project but will be looking at this one again soon. I appreciate all of your assistance.

Maud, I’m still interested in a VBA solution that would negate formula and range adjustments if you still have the time. I planned ahead this year; the sheet isn’t needed until the beginning of October!

Again, thanks everyone.
MM

Don’t forget Module Brando was Excelent in The ExcelFather, too!

Hi Maud,

The markup helper column with formula used appears in the first uploaded version to this post. This will give you the clearest picture of how the markup is used.
Thanks
MM

My apologies, yes, they should be filled in. Posted the sheet before I finished all substitute data. Here is completed sheet.
Thanks
MM

That is correct. The totals that I receive are always based on the Column A business, and no they wouldn’t always be sorted.
Thanks, MM

Thanks for the extra work on this Maud. And of course, that’s never the end of it. I didn’t initially think about a VBA solution so really pared down the data.
The attached file shows the expanded sheet as it will be used, plus it would need to be able to grow with new Site Businesses (column A) and new Businesses (Business A, Business B, etc.).
Is it possible for the VBA to accommodate the full layout and the possible changes?

I can go Zeddy’s route with Ranges if needed.

Thanks
MM

Thanks Maud, will try this on the expanded file.
Appreciate all of your help, always.
MM

Thanks so much Zeddy and Maud. Since I am unfamiliar with VBA, I’ll be using Zeddy’s solution as I need to expand this to cover several types of “costs” with several thousand sites and possibly additional site types. Maud, I still plan on going through your solution to see if I can figure out how to expand for additional sites, etc.

Can’t thank you enough, not only for your Excel skills but your mathematical prowess!
MM

Thanks for the VBA solution Maud.
Since I don’t know VBA, can you describe how the percent and markup is being calculated? And if I need to change the %’s, say A’s will be worth 30% more instead of 25% more, how would that change be accomplished? Also, if a site type other than A, B, C, or D is entered into the sheet it throws an error; is there a simple error handing function to pop up and say “No, wrong site type”?
Thanks for your assist. Always appreciated.
MM

Hi Meleia

..I am looking at three methods of ‘distributing’ the ‘cost’ across the sites based on weighting % and site type.
I need to think about this a little more before I post my recommendation.
I have a method of easily setting the scaling %age values.

zeddy
•Excel Distrubutions Distributor
.

Thank you most Excellent Distrubutions Distributor