Excel Array Question
 

At a new job, there are multiple documents with "supposedly" simila
info that needs updating often. I'd like to create a master tha
populates like fields in other documents.

Do you know if i can use an excel array formula to basically transpos
text? i.e.: If the title in column A = "X" and the partner in colum
B = "Y" then take the details text from column C and paste it here.

Possible?

Please and thank you

--
Message posted from http://www.ExcelForum.com

Excel Array Question
 

=INDEX(Sheet1!$C$1:$C$300,SMALL(IF((Sheet1!$A$1:$A $300="X")*(Sheet1!$B$1:$B$
300="Y"),ROW(1:300)),1))

Enter with Ctrl+Shift+Enter rather than Enter.

This assumes there will only be one row that has X in A and Y in B.

--
Regards,
Tom Ogilvy

"amyacc " wrote in message
...
At a new job, there are multiple documents with "supposedly" similar
info that needs updating often. I'd like to create a master that
populates like fields in other documents.

Do you know if i can use an excel array formula to basically transpose
text? i.e.: If the title in column A = "X" and the partner in column
B = "Y" then take the details text from column C and paste it here.

Possible?

Please and thank you.


---
Message posted from http://www.ExcelForum.com/

Excel Array Question
 

Hi Tom.

Thanks for your help. I tried using that formula and something OD
happened. It should have pulled the data from cell I8 to paste, thi
cell matched the criteria of if "X"&"Y". Instead, it pulled the dat
from cell I13 that did match "X" but not "Y". At first glance ther
doesn't seem to be any reason why that data should have been pulled.

Thoughts?

THANKS

--
Message posted from http://www.ExcelForum.com

Excel Array Question
 

Hi
can you post your exact formula which creates this result.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Tom.

Thanks for your help. I tried using that formula and something ODD
happened. It should have pulled the data from cell I8 to paste, this
cell matched the criteria of if "X"&"Y". Instead, it pulled the data
from cell I13 that did match "X" but not "Y". At first glance there
doesn't seem to be any reason why that data should have been pulled.

Thoughts?

THANKS!


---
Message posted from http://www.ExcelForum.com/

Excel Array Question
 

=INDEX('[Developmen
Update.xls]MASTER'!$I$6:$I$25,SMALL(IF(('[Developmen
Update.xls]MASTER'!$A$6:$A$25="X")*('[Developmen
Update.xls]MASTER'!$G$6:$G$25="Y"),ROW(6:25)),1))

Here's the formula. Again, the answer it's pulling does not only no
contain "Y", that cell in that row is blank. It's not the only cell i
that column that is blank. There really seems to be no reasonin
behind this.

THANKS

--
Message posted from http://www.ExcelForum.com

Excel Array Question
 

you shouldn't use row(6:25). It is used to establish an offset into the
index range - so you should keep it as Row(1:19)

--
Regards,
Tom Ogilvy


"amyacc " wrote in message
...
=INDEX('[Development
Update.xls]MASTER'!$I$6:$I$25,SMALL(IF(('[Development
Update.xls]MASTER'!$A$6:$A$25="X")*('[Development
Update.xls]MASTER'!$G$6:$G$25="Y"),ROW(6:25)),1))

Here's the formula. Again, the answer it's pulling does not only not
contain "Y", that cell in that row is blank. It's not the only cell in
that column that is blank. There really seems to be no reasoning
behind this.

THANKS!


---
Message posted from http://www.ExcelForum.com/

Excel Array Question
 

Hi there.

Your info worked but can you explain why it worked?

Thanks.

-Am

--
Message posted from http://www.ExcelForum.com

Excel Array Question
 

Hi
Tom used the ROW(1:300) as parameter for the SMALL function (cycling
from 1 to 300). So getting the following values:
SMALL(....,1)
SMALL(....,2)
....
SMALL(....,300)

if you change this you don't start with the smalles but in your case
with the 6th smallest

--
Regards
Frank Kabel
Frankfurt, Germany
"amyacc " schrieb im
Newsbeitrag ...
Hi there.

Your info worked but can you explain why it worked?

Thanks.

-Amy


---
Message posted from http://www.ExcelForum.com/