• 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

    Hui...

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

Same directing text in a cell everytime

arifanmol

Member
I wish that in G11 and G12 cells, my formula and text remain the same all the time irrelevant of my worksheet or data
so that i may not need to copy and paste every time
 

Attachments

  • Same and stationary text.xlsx
    9.7 KB · Views: 5
sorry G10 and G11
but see
If there is subtotal anywhere then to its right there should be this formula =SUM(F3:(OFFSET(INDIRECT(ADDRESS(MATCH("Subtotal",F:F,0),6)),-2,0)))
just under it it should have this formula =(SUM(F3:(OFFSET(INDIRECT(ADDRESS(MATCH("Subtotal",F:F,0),6)),-2,0))))*0.18
Moderator note: >> You could use I C O D E - tag to able readable formulas <<
 

Attachments

  • Same and stationary text(2).xlsx
    10.2 KB · Views: 1
Last edited by a moderator:
For the effort spent creating a yellow fill it would have been possible to create a defined name and add meaning to the problem.
For example, using 365,
Code:
= LET(
    taxRate,  18%,
    quantity, CHOOSECOLS(table1, 4),
    unitCost, CHOOSECOLS(table1, 6),
    price,    quantity * unitCost,
    subtotal, SUM(price),
    GST,      taxRate * subtotal,
    VSTACK(subtotal, GST, subtotal+GST)
  )
 
Firstly, the 'code' I showed above is not a macro. It is a regular (but not yet that common) Excel 365 worksheet formula.

LET accepts parameters in twos; the first of each pair is a variable name and the second a formula that is evaluated. Thus the first line sets the tax rate to 18%. The line break in the formula is achieved by typing Alt/Enter. CHOOSECOLS is a new Excel function that allows the user to pick out columns from a 2D array. The next 3 lines are the core calculation which involves no more than simple arithmetic. The final VSTACK just takes the subtotal, the GST and the sum of the two and places them as elements of a 3-cell array which is returned as a dynamic array spilling from the cell with the formula.

Once again, the formula requires Excel 365 to work.
 

Attachments

  • Same and stationary text.xlsx
    13.4 KB · Views: 4
Back
Top