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

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" wrote:

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

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.

"Mexage" wrote:

Dear Barb:

Why don't you try the Index function? The index function will return one row
from a table.

=INDEX('2005 Totals'!$B$3:$B$7,A1)

Where A1 has the number of relative row in the reference.

For example:

A1=2
Step 1. =INDEX('2005 Totals'!$B$3:$B$7,A1)
Step 2. =INDEX('2005 Totals'!$B$3:$B$7,2)
Step 3. ='2005 Totals'!$B$(3+2-1)

Step 4. ='2005 Totals'!$B$4

The INDEX is a one-based function, which means the first element in the list
is 1, not 0.

To use this formula try the following example:
A B C
1 2 =INDEX...
2 4
3 6

1. On A1 type 2, on A2 type 4
2. Select A1:A2 and drag the fill handle down to fill 2,4,6,8...
3. Type the formula mentioned above in B2 (be sure to adjust the table range
to the whole table in '2005 Totals' Sheet)
4. Select B1 and drag the fill handle down.

This will give you in column B the values in the following cells:
'2005 Totals'!$B3
'2005 Totals'!$B5
'2005 Totals'!$B7

Hope that helps; if it does, please rate this post.

G.Morales



"Barb" wrote:

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

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" wrote:

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" wrote:

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