Forum Discussion
Copying formulas with gaps in reference cells?
Sorry if this question has already been asked before, I'm not very good at Excel so I'm honestly not even sure how to describe my question 😞
Basically, I have some samples that I've taken multiple measurements from, and I want to create a separate table that uses all of the measurements from one sample in one formula, like this:
If I try typing the formula once and dragging it down, it only shifts the formula down by one row (instead of by three like I need it to):
Even if I type the correct formula several times and highlight all of that to drag it down so it knows the pattern (the way you can make it count by 5s if you type 5, 10, 15 before you drag it if that makes sense?), it still doesn't shift the formula correctly:
Is there any way to easily copy or otherwise input the formula so that it shifts by the correct number of rows for each sample? My actual data set is way too big to be typing each formula by hand 😞
Thanks!! And please let me know if you need more info I'm not sure I'm explaining the problem very well
=SQRT(INDIRECT(ADDRESS(2+(ROW(A1)-ROW($A$1))*3,COLUMN(C1)))*INDIRECT(ADDRESS(3+(ROW(A1)-ROW($A$1))*3,COLUMN(C1))))/INDIRECT(ADDRESS(4+(ROW(A1)-ROW($A$1))*3,COLUMN(C1)))
This formula returns the intended result in my sheet. The formula in cell L2 is copied across range L2:N5.
OliverScheurich Thank you so much, that works!!
Just so I can actually understand how it works, could I ask what the 2+, 3+, or 4+ are for? How would I need to change them in different scenarios?nevermind, played around with it for another minute and figured it out lol, thanks so much again for your help!!
