Update cell reference within a formula

J

Jean

I have a simple formula in one workbook adding two cells in another
workbook:
=[book1]Sheet1!$A$1+[book1]Sheet1!$A$2

Next month I want to refer to the same workbook, same sheet, but I want
all the rows to shift down by 26:
=[book1]Sheet1!$A$27+[book1]Sheet1!$A$28

There are about forty formulas so I don't want to go in there and
manually add 26 to every little reference every time. Ideas???
thanks-
 
J

Jean

Solved this problem myself----


=OFFSET([book1]Sheet1!$A$1,month*26-26,0)+OFFSET([book1]Sheet1!$A$2,month*26-26,0)

When I assign the month cell to 1 for January, I'll get A1+A2 then in
February month =2 so I'll have A27+A28


Is there a better way?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula fill across workbooks 3
some kind of meta-way to refer to sheet and file names in Excel? 7
Copy Exact Formula 1
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Excel 2007 Problem with SUMIF 2
Offset, References, Help! 5
Creating a linked file based on variable location reference 2
Excel Dragging countif formula with changing criteria 3

Top