Using a variable as a row reference?
 

I have a problem. When I type the formula

=IF(SUM(index(AidElement1,17,1),index(AidElement2, 17,1),...,index(AidElement25,17,1))=0,"NB","_1040_ Income")

into my first row (17) and try to use the Fill command to paste the
formula to my other rows (18-500), the row_num reference (17) does not
change. Is there a way to change the row_num reference so that it's
relative instead of absolute?

Thanks.

"Tom Ogilvy" wrote in message ...
=IF(SUM(index(AidElement1,rownum,1),index(AidEleme nt2,rownum,1),index(AidEle
ment3,rownum,1),...,index(AidElement25,rownum,1))= 0,"NB","_1040_Income")

Regards,
Tom Ogilvy



mjmorrison wrote in message
...
Is there any way to name columns in Excel without naming ranges? In
order to make my spreadsheet formulas more readable, I tried to
replace cell references with named ranges; I selected each (entire)
column, renamed it, and then changed the appropriate cell references
to their new names. However, this technique did not work with each
formula. For example, I have a formula that includes the following
test:


=IF(SUM(AidElement1,AidElement2,AidElement3,...,Ai dElement25)0,"NB","_1040_I
ncome")

The problem is thus: Since there is at least one non-zero element for
each named column (AidElement1, AidElement2, etc.) in -some- row, the
test always evaluates to true. However, some rows have zero entries
for each of the named columns, and therein lies my problem. Is there a
way that I can name just a column, so that when I use the name as a
reference only the column is fixed but I can include a variable for
the row?

Thanks.

Using a variable as a row reference?
 

In youre formula, instead of 17 use: Row() With out argument will return the
row number
that it resides in.

In row seventeen it will be 17 and in row eighteen it will be 18 and so
on...

Wendel


"mjmorrison" skrev i meddelandet
om...
I have a problem. When I type the formula


=IF(SUM(index(AidElement1,17,1),index(AidElement2, 17,1),...,index(AidElement
25,17,1))=0,"NB","_1040_Income")

into my first row (17) and try to use the Fill command to paste the
formula to my other rows (18-500), the row_num reference (17) does not
change. Is there a way to change the row_num reference so that it's
relative instead of absolute?

Thanks.

"Tom Ogilvy" wrote in message
...

=IF(SUM(index(AidElement1,rownum,1),index(AidEleme nt2,rownum,1),index(AidEle
ment3,rownum,1),...,index(AidElement25,rownum,1))= 0,"NB","_1040_Income")

Regards,
Tom Ogilvy



mjmorrison wrote in message
...
Is there any way to name columns in Excel without naming ranges? In
order to make my spreadsheet formulas more readable, I tried to
replace cell references with named ranges; I selected each (entire)
column, renamed it, and then changed the appropriate cell references
to their new names. However, this technique did not work with each
formula. For example, I have a formula that includes the following
test:



=IF(SUM(AidElement1,AidElement2,AidElement3,...,Ai dElement25)0,"NB","_1040_I
ncome")

The problem is thus: Since there is at least one non-zero element for
each named column (AidElement1, AidElement2, etc.) in -some- row, the
test always evaluates to true. However, some rows have zero entries
for each of the named columns, and therein lies my problem. Is there a
way that I can name just a column, so that when I use the name as a
reference only the column is fixed but I can include a variable for
the row?

Thanks.

Using a variable as a row reference?
 

Use the ROW() function

IF(SUM(index(AidElement1,ROW(),1),index(AidElement 2,ROW(),1),...,index(AidEl
ement25,ROW(),1))=0,"NB","_1040_Income")

Not tested, but should be okay
--

HTH

Bob Phillips

"mjmorrison" wrote in message
om...
I have a problem. When I type the formula


=IF(SUM(index(AidElement1,17,1),index(AidElement2, 17,1),...,index(AidElement
25,17,1))=0,"NB","_1040_Income")

into my first row (17) and try to use the Fill command to paste the
formula to my other rows (18-500), the row_num reference (17) does not
change. Is there a way to change the row_num reference so that it's
relative instead of absolute?

Thanks.

"Tom Ogilvy" wrote in message
...

=IF(SUM(index(AidElement1,rownum,1),index(AidEleme nt2,rownum,1),index(AidEle
ment3,rownum,1),...,index(AidElement25,rownum,1))= 0,"NB","_1040_Income")

Regards,
Tom Ogilvy



mjmorrison wrote in message
...
Is there any way to name columns in Excel without naming ranges? In
order to make my spreadsheet formulas more readable, I tried to
replace cell references with named ranges; I selected each (entire)
column, renamed it, and then changed the appropriate cell references
to their new names. However, this technique did not work with each
formula. For example, I have a formula that includes the following
test:



=IF(SUM(AidElement1,AidElement2,AidElement3,...,Ai dElement25)0,"NB","_1040_I
ncome")

The problem is thus: Since there is at least one non-zero element for
each named column (AidElement1, AidElement2, etc.) in -some- row, the
test always evaluates to true. However, some rows have zero entries
for each of the named columns, and therein lies my problem. Is there a
way that I can name just a column, so that when I use the name as a
reference only the column is fixed but I can include a variable for
the row?

Thanks.