Forum Discussion

bryen79's avatar
bryen79
Copper Contributor
Oct 29, 2022
Solved

How to duplicate a block of cells without losing formula references

Please note: I have posted this question on another website a few days ago and have not received any answers to it. I am hoping on of you kind folks here might know the answer. I am creating time ...
excel
Formulas and Functions
bryen79's avatar
bryen79
Copper Contributor
Oct 29, 2022
OMG you're the best!!!!! I've never used INDEX or QUOTIENT before but this is perfect!! I texted it by adding another person for each day and it pulled the correct data as I used unique but random times to verify! Works like a charm! My hats off to you!!
bryen79's avatar
bryen79
Copper Contributor
Oct 29, 2022

Hi, sorry, if I may, could I ask for you to explain what these numbers do in the formula please?

-14,31)+3,COLUMN(C13)-3)


Once I understand them, I can tweak them for a different excel file I am working on.  Thank you!

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 29, 2022

    bryen79 

    In =INDEX(Sunday!$C:$J,QUOTIENT(ROW(D14)-14,31)+3,COLUMN(D14)-3):

     

    QUOTIENT(ROW(D14)-14,31)+3 is the row number within Sunday!C:J

    ROW(D14) is the row number of the cell with the formula. This will automatically be adjusted when you copy this down: in D45 it will be ROW(D45) etc.

    We subtract the row number of the first row with such a formula: 14

    In D14, ROW(D14)-14 = 0, in D45, ROW(D45)-14 =31, etc.

    QUOTIENT(...,31) performs integer division: it divides the first argument by 31 and returns only the whole number part. In D14 this will be 0, in D45 it will be 1, etc.

    Finally, we add 3 to get the correct row number on the Sunday sheet: for D14 it is 0+3 = 3, for D45 it is 1+3 = 4, etc.

     

    COLUMN(D14)-3 is the column number within Sunday!C:J. In D13, it is 4-3 = 1, so we refer to the 1st column (C). When we copy the formula to E14, we get COLUMN(E14)-3 = 5-3 = 2, so we refer to the 2nd column in Sunday!C:J, i.e. column D, etc.

     

    Does that help?

Resources