Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Array Question
Hi there.
Your info worked but can you explain why it worked? Thanks. -Am -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array question | Excel Discussion (Misc queries) | |||
array question | Excel Worksheet Functions | |||
Array question | Excel Programming | |||
vba array question | Excel Programming | |||
Is this an array question? | Excel Programming |