CODEWITHSUNDEEP
excelexcel-formulavba
Matt
Matt

Reputation: 23

Dynamic Formulas

I have a sheet called Column Reo on this sheet is a whole lot of data entry because on concrete column properties...

Eg:

  1. width
  2. depth
  3. height
  4. no. of vertical reinforcement bars
  5. dia of bars

I also have a second sheet that has fixed data for some named rages etc on it...

Eg:

  1. width
  2. constants of reinforcement bars
  3. ligature pattern type calcs

etc.

Each lig pattern needs a different formula based on shape of column and columns structural loading etc. There are 27 different lig pattern types.

I have written formulas on the "fixed data" sheet that would calculate the total length of each lig pattern based on the concrete columns properties and then created a drop down menu on the "column reo" sheet that uses VLOOKUP to match that up, problem is I want to drag across the actual formula so that it dynamically adjusts to the row that it's been copied to and uses the data from that row to calculate the total lig length.

I hope that explains it properly, I am hoping it can be done without VBA but if that is needed, so be it!

EDIT Here is a link for the file https://www.dropbox.com/s/c29e0s4g13cr89p/Reo%20calculation%20sheet.xlsx

Based on the selection from "column reo"-H4 drop down menu I want to be able to pull across the formula from "fixed data" corresponding table. But I need the formula to adjust for the row that it is on.

Upvotes: 0

Views: 266

Answers (2)

user3616725
user3616725

Reputation: 3655

My solution rellies on EVAL() function from Morefunc Addon*

  1. Re-write your formulas on the fixed data sheet like shown below. Including the quotes - the cells are supposed to just store this as text, not actually perform any calculations:

    this is a new representation of your formula in K5 (to be pasted in L5): "+((C"&ROW()&"*2)+(D"&ROW()&"4*2))+((C"&ROW()&"/3*2)+(D"&ROW()&"*2))"

  2. Make sure you have MORFUNC working (try writing `=Eval(" and see if it is recognised as a function

  3. on column reo sheet write =EVAL(EVAL(VLOOKUP(H4,'fixed data'!J4:L33,3,FALSE))) in cell K4. This will check which "Col type" in selected in corresponding dropdown, and then use the new formulas to calculate corresponding "Lig Pattern"

  4. repeat for other formulas on "fixed data" sheet, and copy-paste the formula on "column reo" sheet for all other lines.

MOREFUNC ADDON

Upvotes: 0

Captain
Captain

Reputation: 2238

As a dirty workaround without VBA, you could have all the types calculations as separate columns (in column reo sheet), then use the drop-down to pick which of these you want returned for that row... Ugly, but simple.

If you are happy to use VBA...

  1. Enter your functions in your "fixed data" lookup as text (apostrophe at the start) like this '=+(('column reo'!C<<row>>*2)+('column reo'!D<<row>>*2))+(('column reo'!C<<row>>/3*2)+('column reo'!D<<row>>*2)) (note that the row number in the formula is replaced with <<row>>

  2. In VBA, create the function calculateLig:
    Function calculateLig(r As Range) As Double
    Pattern = Application.WorksheetFunction.VLookup(r, Application.Worksheets("fixed data").Range("J:K"), 2, 0)
    Pattern = Replace(Pattern, "<<row>>", r.Row)
    calculateLig = Evaluate(Pattern)
    End Function
    This function does the VLOOKUP (hardcoded range - you could change that) to get back the text formula. It then replaces the keyword <<row>> in the formula with the row of the input cell and returns the evaluation of it. You can use this function as any other in your worksheet formula.

Upvotes: 1

Related Questions