help with offset function

B

Barb

I am trying to add 2 different wrk shts to a 3rd wrk sheet, one work sheet
(total 2005 has 2 lines for each company)
=('2005 Totals'!$B3+'Maintenance Dollars'!$B2)
=('2005 Totals'!$B5+'Maintenance Dollars'!$B3)
=('2005 Totals'!$B7+'Maintenance Dollars'!$B4)
as you can see the 2005 totals must skip a line, I've tried the offset in a
few ways (I start with B3 for the first formula because that is where the
value is), I just can't seem to get it to work so that I don't have to type
this formula in for every line, I just get error messages, anyone out there
who knows how to write the offset formula for me??? Thank you! Have a great
day!
Barb
 
A

Alok

Hi
If you are putting the formula anywhere in Row 1 then the formula is

=OFFSET('[2005 totals.xls]Sheet1'!$B2,1+(ROW()-1)*2,0)+'[Maintenance
Dollars.xls]Sheet1'!$B2

if you are entering it in row 2 then it will be
=OFFSET('[2005 totals.xls]Sheet1'!$B2,1+(ROW()-2)*2,0)+'[Maintenance
Dollars.xls]Sheet1'!$B2

Alok Joshi
 
B

Barb

Not familiar with index. But I'm trying to total 2 different columns in 2
different worksheets into a 3rd worksheet =('2005 Totals'!$B3+'Maintenance
Dollars'!$B2) into a 3rd sheet. Then copying the formula down, but the 2005
Totals spreadsheet skips lines. So I'm having to manually change it because
when you copy down it assumes you are going line to line not every other line.
 
B

Barb

Alok YOU'RE AN ANGEL!!! Thank you so very much!!!! The only adjustment I made
was to put a $ after the B to make the numbers right. This is the second time
you have solved my offset problem. Maybe sooner or later I'll be able to do
it on my own.

Have a wonderful day!!!!

Alok said:
Hi
If you are putting the formula anywhere in Row 1 then the formula is

=OFFSET('[2005 totals.xls]Sheet1'!$B2,1+(ROW()-1)*2,0)+'[Maintenance
Dollars.xls]Sheet1'!$B2

if you are entering it in row 2 then it will be
=OFFSET('[2005 totals.xls]Sheet1'!$B2,1+(ROW()-2)*2,0)+'[Maintenance
Dollars.xls]Sheet1'!$B2

Alok Joshi

Barb said:
I am trying to add 2 different wrk shts to a 3rd wrk sheet, one work sheet
(total 2005 has 2 lines for each company)
=('2005 Totals'!$B3+'Maintenance Dollars'!$B2)
=('2005 Totals'!$B5+'Maintenance Dollars'!$B3)
=('2005 Totals'!$B7+'Maintenance Dollars'!$B4)
as you can see the 2005 totals must skip a line, I've tried the offset in a
few ways (I start with B3 for the first formula because that is where the
value is), I just can't seem to get it to work so that I don't have to type
this formula in for every line, I just get error messages, anyone out there
who knows how to write the offset formula for me??? Thank you! Have a great
day!
Barb
Click to expand...
Click to expand...
 
Top