Forum Discussion

ABlea010's avatar
ABlea010
Copper Contributor
Jan 17, 2024
Solved

Update formulas automatically when in/out sample size changes

I have been trying to figure out if this is possible and will try to explain the best I can. I have a worksheet where my In and out sample sizes change daily. I have a separate worksheet to calculate...
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jan 17, 2024

    yes you can. the return of a INDEX() formula is actually a cell reference and can be use in the range so try something like:
    =RSQ(
    INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
    INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample End]),
    INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
    INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample End]))

    that is based on A1 having the date you want to use and the above table being called 'table'

mtarler's avatar
mtarler
Silver Contributor
Jan 17, 2024

yes you can. the return of a INDEX() formula is actually a cell reference and can be use in the range so try something like:
=RSQ(
INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample End]),
INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample End]))

that is based on A1 having the date you want to use and the above table being called 'table'

  • ABlea010's avatar
    ABlea010
    Copper Contributor
    Jan 19, 2024
    You are my hero lol

Resources