Search code examples
excelexcel-formulaexcel-2007

How to Drag a formula in Excel both across the columns and rows?

I have an Excel 2007 file with 2 sheets. The first sheet has 2 columns: one of dates, one of numbers like so:

1/1/2017    37    
1/2/2017    82    
1/3/2017    96
...

The second sheet has is to store the numbers in a "Calendar-like" fashion, like so:

                     SUN   MON   TUE   WED   THR   FRI   SAT
1/1/2017-1/7/2017    37    82    96    23    54    25    97
1/8/2017-1/14/2017   49    76    65    13    12    14    96
...

I am filling the "Calendar" style sheet from the much larger "List" style sheet. These go on for years, so I need a simple way to fill the "Calendar" style sheet from the plain list. I can use the simple formula of =sheet1!D7 to get from one sheet to the other, but I can only drag that formula in one direction... I can start it on Sunday, Jan 1 and drag it the whole way to Saturday, Jan 7, but if I try to drag the formula from that row to the next, it tries to insert starting with Jan 2 instead of Jan 8:

                     SUN   MON   TUE   WED   THR   FRI   SAT
1/1/2017-1/7/2017    37    82    96    23    54    25    97
1/8/2017-1/14/2017   82    96    23    54    25    97    61
...

Can I get these values from the list-type sheet to the calendar-type sheet without typing the formula in for each and every cell?

Solution

  • Not sure how your data is laid out, but if you adjust the range, this index/match should work:

    =INDEX($B$1:$B$31,MATCH(DATEVALUE(LEFT($E3,SEARCH("-",$E3)-1))+COLUMN()-6,$A$1:$A$31,FALSE))

    enter image description here

    Note: I have one magic number in the formula, a 6. This is because this is where your "Sun" column starts. If you put your formula in any other column, adjust that number. Alternatively, you could get fancy and add logic looking for "Sun" in some array, but thought that's a little overkill.

    You can drag this formula over and down.