hi,

i may not be very successful in explaining what i need, but here i go:

Col A Col B Col C
10 1A X
20 2B Y
30 2B X

i need to have a formula in a cell on another sheet such that, if the
row contains 1A and X, or 2B and X, i would like (10, 30) to appear as
a result of the formula.

what i mean is:

if sheet2.cell(a1) has the formula '=IF(ColC = "X", ..., ...), the
result in the cell should look like 10, 30.

i have a macro which does this now, but i would like for a formula to
be able to do this in order to obviate the need for a macro.

thanks,

mac.


--
icestationzbra
------------------------------------------------------------------------
icestationzbra's Profile: http://www.excelforum.com/member.php...fo&userid=4580
View this thread: http://www.excelforum.com/showthread...hreadid=486171

If I understand correctly,

=IF(AND(OR(B1="1a",B1="2b"),C1="x"),"10, 30","")

which for another sheet would become

=IF(AND(OR(Sheet1!B1="1a",Sheet1!B1="2b"),Sheet1!C 1="x"),"10, 30","")


icestationzbra Wrote:
hi,

i may not be very successful in explaining what i need, but here i go:

Col A Col B Col C
10 1A X
20 2B Y
30 2B X

i need to have a formula in a cell on another sheet such that, if the
row contains 1A and X, or 2B and X, i would like (10, 30) to appear as
a result of the formula.

what i mean is:

if sheet2.cell(a1) has the formula '=IF(ColC = "X", ..., ...), the
result in the cell should look like 10, 30.

i have a macro which does this now, but i would like for a formula to
be able to do this in order to obviate the need for a macro.

thanks,

mac.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=486171

Mac,
If I understand your question correctly, then what you want to do
cannot be done simply with formulas. One basic reason is that the
CONCATENATE() function in Excel does not work similar to aggregate
functions like SUM(). Once I tried to solve this problem using Circular
References and Iteration mode, but it was for the concept only. Any
such solution would probably provide you with more complexity than you
currently want to avoid.

Kostis Vezerides

hi bryan,

thanks for your reply. unfortunately, this may not be the solution to
my problem for two reasons:

1. i have over 1000 rows, ever-burgeoning database.
2. on a daily basis the rows with "X" change. today there may be 10
such rows, tommorrow 54, day after 23. hence, i cannot 'fixate' Column
A data in the "IF" condition.

i was thinking that if i were to get a formula to get the index in
Column A (comma separated) i would use the 'Recalculate' functionality
in conjunction with it to get the current indices (viz, rows with
"X").

thanks,

mac.


--
icestationzbra
------------------------------------------------------------------------
icestationzbra's Profile: http://www.excelforum.com/member.php...fo&userid=4580
View this thread: http://www.excelforum.com/showthread...hreadid=486171

hi Kostis Vezerides,

i concur with with you. here, in this case, the simpler the solution,
the more complex it actually gets. hence, i guess i will stick to the
macro i have working right now.

mac.


--
icestationzbra
------------------------------------------------------------------------
icestationzbra's Profile: http://www.excelforum.com/member.php...fo&userid=4580
View this thread: http://www.excelforum.com/showthread...hreadid=486171