• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help in Formula

Ateeb Ali

Dear Sir,
Attached is file, I need some helps.

1. Based on limited skills on MS excel, I have developed a calculation sheet.
2. You can find blank sheet of Result, This should show following column, so need help in formula, data need to be fetched from "Calculation Sheet";
Quality & Tex Column E&G & F&H
Total Meter Column I & J

Also if there is better way then validation to select stitch code, can I have multi option box in each cell instead of validation, this will ease out selection.

Then I need some vbcodes to blank the data, save the file with style name and ro enter data of another style.

Thanks everyone in advance


Peter Bartholomew

Well-Known Member
@Ateeb Ali
I suggest you fill in your results table with hand-calculated values to show what it is that you are hoping to see. Also do not forget that the technical aspects of your business area will mean little to anyone else. e.g. Does the sheet with the multitude of stitch types represent one garment of a given style?

Once you have a working document, then might be the time to tweak it to provide an improved user experience.

Ateeb Ali

Dear Sir,
Thanks for your response, attached file is with result, I just need to learn some automation which I can do in this file for immediate calculation.

I want to make it like software so everyone can use it with ease.

Check Sheet, Result



Peter Bartholomew

Well-Known Member
Hi Ateeb

Firstly, you stated objective of producing an 'app' that is built on Excel as a platform but behaves like software, rather than being presented to the user in terms of a spreadsheet, is perfectly achievable.

Your question is very different from the normal run of "How do I write a formula to ...?" questions that this forum normally deals with and later on you will have questions you need to address to the "VBA questions" forum.

As a software solution, you will not be exposing the whole of calculation so you need to plan the user roles and interactions.

1. I assume the 'Calculation Guideline' is reference data that will rarely change and keeping it updated is a developer responsibility.

2. The Calculation Sheet appears to have a dual role. When gathering information for a new style it serves as a user form but there is also a some minor calculation and lookup. To aggregate the thread lengths for a particular style probably requires a SUMIFS formula for each 60 and 80 Tex but I have no idea whether the values under 'Needle' should be added with those from 'Bobbin / Louper' or whether they are alternatives.

Either way, is it essential that these data entry and calculation functions are performed within a sequence of tables within a single sheet? Could you have a new sheet (copied from a template sheet) for each garment style? That would still allow you to check the data and calculation at a later date but avoid creating a 'monster' sheet that is difficult to reference. The alternative of a unique sheet that performs a calculation and is cleared once the results are transferred by VBA is simpler in some ways but leaves you no record of the data input used to generate the result.

3. The result sheet again serves two roles. It is a data repository giving thread consumption for all styles but it is formatted as a sequence of reports rather than a data table. This is feasible but it makes the task of populating the report tables and then finding data corresponding to a given style more complicated than it need be.

VBA. As far as VBA automation is concerned, tasks may be initiated by clicking on a button or a shape or by changing the data within a cell or even by monitoring when the user selects a given cell.

If communication between VBA and worksheet cells is needed I would ensure that referencing is always done using defined names or structured references. This is because VBA code does not update to match layout adjustments made to the worksheet, so direct cell references of the kind Range("H28")[alternatively Range("H" & rowNum) or even CELLS( rowNum, 8 ) ] are likely to produce errors both during development and use.

No doubt you will have some more specific questions as you go along.

Ateeb Ali

Dear Sir,
Thanks for your detailed reply.

Can you help me writing a formula first which can collect data from Calculation Sheet' Column E and F like;
Spun Polyester or Core Spun
Then on next column, it should compile data from Column: G & H like ;
Tex 60 or Tex 80
So it will make some unique values like;
*(x)Spun Polyester Tex 60
*(y)Core Spun Tex 80
Then on next cell it should show its sum value from column number I & J, it should some all values of (x) and (y)

Peter Bartholomew

Well-Known Member
I have suggested sme formulas that perform the calculation you require.

To understand the formula you will need to know the following:
1. Structured references (bearing in mind that the table headings have been hidden)
2. The SUMIFS function, here applied over 2D ranges.
3. The use of Named relative formulas for 'Tex' and 'Quality'

= SUMIFS( S_159_1300[[Thread.Needle]:[Thread.Bobbin]],
S_159_1300[[Size.Needle]:[Size.Bobbin]], Tex,
S_159_1300[[Quality.Needle]:[Quality.Looper]], Quality )