Conditional Calculation

  • Thread starter Thread starter Mary Beth
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top