collating info from index column

[icestationzbra]

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]

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!C1="x"),"10, 30","")

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]

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]

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.

[vezerid]

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