Conditional Calculation

M

Mary Beth

The conditional statement I'm working with is:

=IF(AND(A3=A2,B3=B2), 'Au'!E1,'Au'!E2)

Here's the problem, I want to fill the column with this
conditional statement but if I do that it will
automatically convert E1 to E2 then E3 ect like it was
filling a series. So instead of getting 5 rows with E2
then swithcing to E3 for 6 rows, I get E1, E2, E3.

I want the value to be E1 until AND(A3=A2,B3=B2)is false
and then I want to go to the next value (E3). Again,
filling E3 in until AND(Ax=Ax,Bx=Bx) is false and then
switching to E4 ect.

Is there a way to write this so that it doesn't simply
treat the statement as a series when I drag the formula
to other cells? If I use "$" to keep the formula from
changing, it works for the first two in the series, but
then of course won't change for the next values (E3 and
higher).

Thanks,

mb
 
N

Niek Otten

Hi Mary Beth,

Easiest is to introduce one extra column (which you can hide if necessary).
In c2, enter 1.
In c3:
=IF(AND(A3=A2,B3=B2),C2,C2+1)
Your formula to copy down in D3:
=INDIRECT("Au!e"&C3)
Keep the single quotes around the sheetname, although they don't show here

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
M

Mary Beth

Thanks so much for you help, I never knew about INDIRECT
before. Now, if I could just get it to work...

I can't seem to figure out what the reference is actually
referring to. Would you mind explaining "=INDIRECT("Au!
e"&C3)" a bit more? Specifically the "&". I see it in
the help menu file, but it is not explained and I've
never used it in a cell before so I'm not really sure
what it is doing. I'm hoping if I see the connection I
can figure out how to correctly reference my cells here
(I keep getting he #REF! error here).

Thanks so much.
mary beth
 
M

Mary Beth

Since I go back and read the archives of this forum, I
thought I would post a final "answer" to my probem in
case someone else stumbles upon this one day. I found a
great and simple webpage that explained the INDIRECT
function:

http://www.cpearson.com/excel/indirect.htm

In this example =INDIRECT("A"&C3), I am saying let C3
determine the row value for the column A. So if C3
equals 4, then the cell will equal A4; if C3 equals 17,
then the cell will equal A17.

Thanks for people's help.

mb
 
Top