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","")

 

[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

 

[icestationzbra]

hi bryan,

thanks for your reply. unfortunately, this may not be the solution t
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 1
such rows, tommorrow 54, day after 23. hence, i cannot 'fixate' Colum
A data in the "IF" condition.

i was thinking that if i were to get a formula to get the index i
Column A (comma separated) i would use the 'Recalculate' functionalit
in conjunction with it to get the current indices (viz, rows wit
"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 th
macro i have working right now.

mac