how to make cell address reference increment?
 

hi, this shld be easiest one... i read thru the excel help file, but dun
really understand on which function to use...

say i hv a formula =IF(A1=B1,C1+1,D1). in this case, the result will be
value of C1 plus 1 if the logic is true. but my intention is actually to make
increment on either the row or column to C1, which the expected result shld
be equal to C2 (row) or D1 (column). which function shld i use for this?

thanks in advance for help!!

Try use indirect function and address function!

=INDIRECT(ADDRESS(rowno,3))
You can dynamically change row number: rowno

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist

www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====

"jacko" wrote in message
...
hi, this shld be easiest one... i read thru the excel help file, but dun
really understand on which function to use...

say i hv a formula =IF(A1=B1,C1+1,D1). in this case, the result will be
value of C1 plus 1 if the logic is true. but my intention is actually to
make
increment on either the row or column to C1, which the expected result
shld
be equal to C2 (row) or D1 (column). which function shld i use for this?

thanks in advance for help!!

=IF(A1=B1,ROW()+1,COLUMN()+1)

--
HTH

Bob Phillips

"jacko" wrote in message
...
hi, this shld be easiest one... i read thru the excel help file, but dun
really understand on which function to use...

say i hv a formula =IF(A1=B1,C1+1,D1). in this case, the result will be
value of C1 plus 1 if the logic is true. but my intention is actually to
make
increment on either the row or column to C1, which the expected result
shld
be equal to C2 (row) or D1 (column). which function shld i use for this?

thanks in advance for help!!

Jacko,

=IF(A1=B1,C2,D1)

But if you have some other incrementing scheme:

=OFFSET(C1,(A1=B1)*1,(A1<B1)*1)

=OFFSET(C1,(A1=B1)*2,(A1<B1)*2)
=OFFSET(C1,(A1=B1)*(other formula),(A1<B1)*(other formula))

HTH,
Bernie
MS Excel MVP


"jacko" wrote in message
...
hi, this shld be easiest one... i read thru the excel help file, but dun
really understand on which function to use...

say i hv a formula =IF(A1=B1,C1+1,D1). in this case, the result will be
value of C1 plus 1 if the logic is true. but my intention is actually to
make
increment on either the row or column to C1, which the expected result
shld
be equal to C2 (row) or D1 (column). which function shld i use for this?

thanks in advance for help!!