Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
I know the fix is easy, but I can't seem to copy my array formula. In sum, I
am trying to have a column check two other columns. Based on the returned values, I expect my value. Following is sample data: AAC Company Type Coke M113 Pepsi M114 Sprite M115 Cola M116 For instance, if it matches "Coke" and M113, then AAC should have "Widget". For Pepsi and M114, then AAC might be "Stuff", etc. On another tab, I have range names to capture the return value. The formula (which works on a single cell) I have in Col A (AAC) is {=INDEX(AAC, MATCH(B2&C2,Company&Type,0))} On the other tab, I have the lookup values (with range names). See following: AAC Company Type Widget Coke M113 Stuff Pepsi M114 More Stuff Sprite M115 Books Cola M116 Cans Coke M123 Bottles Pepsi M124 Chairs Sprite M125 Lights Cola M126 Again, my formula works if I use it on one cell (using CTRL+SHIFT+ENTER), but when I copy to other rows I get the same result as the orginal (copied cell). I know it is an easy solution, but its not working for me. I appreciate any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
How are you copying?
I entered everything the way you described with the array formula in A2. Then I copied A2 to A3:A5 and it worked. Download the sample file from http://wikisend.com/download/553030/Sample.xls You can go to A3 and press CTRL-D or Drag the handle from A2 down to A3:A5 or Copy A2 and paste over A3:A5 Are you selecting the formula and then pasting it as text in other cells? "Scott A" wrote: I know the fix is easy, but I can't seem to copy my array formula. In sum, I am trying to have a column check two other columns. Based on the returned values, I expect my value. Following is sample data: AAC Company Type Coke M113 Pepsi M114 Sprite M115 Cola M116 For instance, if it matches "Coke" and M113, then AAC should have "Widget". For Pepsi and M114, then AAC might be "Stuff", etc. On another tab, I have range names to capture the return value. The formula (which works on a single cell) I have in Col A (AAC) is {=INDEX(AAC, MATCH(B2&C2,Company&Type,0))} On the other tab, I have the lookup values (with range names). See following: AAC Company Type Widget Coke M113 Stuff Pepsi M114 More Stuff Sprite M115 Books Cola M116 Cans Coke M123 Bottles Pepsi M124 Chairs Sprite M125 Lights Cola M126 Again, my formula works if I use it on one cell (using CTRL+SHIFT+ENTER), but when I copy to other rows I get the same result as the orginal (copied cell). I know it is an easy solution, but its not working for me. I appreciate any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
I appreciate the very timely response.
I agree and almost understand everything with this formula. While I am no expert, I am no beginner either. This makes it even harder to understand why this isn't working with my actual data. I am still getting #N/A (which I failed to mention in the original post). I have tried CTRL-C & CTRL-V, dragging the bottom right corner, and CTRL-C and Paste Special Formula. All without luck. This absolutely boggles my mind. As there a particular cell format required (i.e., text, general, etc)? I've also to see if there was an option that needed to be enabled or disabled. I have no clue wha tthe problem is. I appreciate your help. "Sheeloo" wrote: How are you copying? I entered everything the way you described with the array formula in A2. Then I copied A2 to A3:A5 and it worked. Download the sample file from http://wikisend.com/download/553030/Sample.xls You can go to A3 and press CTRL-D or Drag the handle from A2 down to A3:A5 or Copy A2 and paste over A3:A5 Are you selecting the formula and then pasting it as text in other cells? "Scott A" wrote: I know the fix is easy, but I can't seem to copy my array formula. In sum, I am trying to have a column check two other columns. Based on the returned values, I expect my value. Following is sample data: AAC Company Type Coke M113 Pepsi M114 Sprite M115 Cola M116 For instance, if it matches "Coke" and M113, then AAC should have "Widget". For Pepsi and M114, then AAC might be "Stuff", etc. On another tab, I have range names to capture the return value. The formula (which works on a single cell) I have in Col A (AAC) is {=INDEX(AAC, MATCH(B2&C2,Company&Type,0))} On the other tab, I have the lookup values (with range names). See following: AAC Company Type Widget Coke M113 Stuff Pepsi M114 More Stuff Sprite M115 Books Cola M116 Cans Coke M123 Bottles Pepsi M124 Chairs Sprite M125 Lights Cola M126 Again, my formula works if I use it on one cell (using CTRL+SHIFT+ENTER), but when I copy to other rows I get the same result as the orginal (copied cell). I know it is an easy solution, but its not working for me. I appreciate any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
Did you download my sample file?
Either mail or upload your file so that I can see what is wrong. I simply copied and pasted the data in your post and it worked for me. This is the formula I have =INDEX(AAC, MATCH(B2&C2,Company&Type,0)) "Scott A" wrote: I appreciate the very timely response. I agree and almost understand everything with this formula. While I am no expert, I am no beginner either. This makes it even harder to understand why this isn't working with my actual data. I am still getting #N/A (which I failed to mention in the original post). I have tried CTRL-C & CTRL-V, dragging the bottom right corner, and CTRL-C and Paste Special Formula. All without luck. This absolutely boggles my mind. As there a particular cell format required (i.e., text, general, etc)? I've also to see if there was an option that needed to be enabled or disabled. I have no clue wha tthe problem is. I appreciate your help. "Sheeloo" wrote: How are you copying? I entered everything the way you described with the array formula in A2. Then I copied A2 to A3:A5 and it worked. Download the sample file from http://wikisend.com/download/553030/Sample.xls You can go to A3 and press CTRL-D or Drag the handle from A2 down to A3:A5 or Copy A2 and paste over A3:A5 Are you selecting the formula and then pasting it as text in other cells? "Scott A" wrote: I know the fix is easy, but I can't seem to copy my array formula. In sum, I am trying to have a column check two other columns. Based on the returned values, I expect my value. Following is sample data: AAC Company Type Coke M113 Pepsi M114 Sprite M115 Cola M116 For instance, if it matches "Coke" and M113, then AAC should have "Widget". For Pepsi and M114, then AAC might be "Stuff", etc. On another tab, I have range names to capture the return value. The formula (which works on a single cell) I have in Col A (AAC) is {=INDEX(AAC, MATCH(B2&C2,Company&Type,0))} On the other tab, I have the lookup values (with range names). See following: AAC Company Type Widget Coke M113 Stuff Pepsi M114 More Stuff Sprite M115 Books Cola M116 Cans Coke M123 Bottles Pepsi M124 Chairs Sprite M125 Lights Cola M126 Again, my formula works if I use it on one cell (using CTRL+SHIFT+ENTER), but when I copy to other rows I get the same result as the orginal (copied cell). I know it is an easy solution, but its not working for me. I appreciate any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
I did download your sample file. I did the same thing and it worked with my
sample build, just not with my actual file. The following link is my actual data file http://wikisend.com/download/540472/Breakout.xls Col A is the column that I want to read two other cells to match. It should look at the values in Col J & L. My ranges are on the other sheet. If you can get it to work I would really appreciate it. Scott "Sheeloo" wrote: Did you download my sample file? Either mail or upload your file so that I can see what is wrong. I simply copied and pasted the data in your post and it worked for me. This is the formula I have =INDEX(AAC, MATCH(B2&C2,Company&Type,0)) "Scott A" wrote: I appreciate the very timely response. I agree and almost understand everything with this formula. While I am no expert, I am no beginner either. This makes it even harder to understand why this isn't working with my actual data. I am still getting #N/A (which I failed to mention in the original post). I have tried CTRL-C & CTRL-V, dragging the bottom right corner, and CTRL-C and Paste Special Formula. All without luck. This absolutely boggles my mind. As there a particular cell format required (i.e., text, general, etc)? I've also to see if there was an option that needed to be enabled or disabled. I have no clue wha tthe problem is. I appreciate your help. "Sheeloo" wrote: How are you copying? I entered everything the way you described with the array formula in A2. Then I copied A2 to A3:A5 and it worked. Download the sample file from http://wikisend.com/download/553030/Sample.xls You can go to A3 and press CTRL-D or Drag the handle from A2 down to A3:A5 or Copy A2 and paste over A3:A5 Are you selecting the formula and then pasting it as text in other cells? "Scott A" wrote: I know the fix is easy, but I can't seem to copy my array formula. In sum, I am trying to have a column check two other columns. Based on the returned values, I expect my value. Following is sample data: AAC Company Type Coke M113 Pepsi M114 Sprite M115 Cola M116 For instance, if it matches "Coke" and M113, then AAC should have "Widget". For Pepsi and M114, then AAC might be "Stuff", etc. On another tab, I have range names to capture the return value. The formula (which works on a single cell) I have in Col A (AAC) is {=INDEX(AAC, MATCH(B2&C2,Company&Type,0))} On the other tab, I have the lookup values (with range names). See following: AAC Company Type Widget Coke M113 Stuff Pepsi M114 More Stuff Sprite M115 Books Cola M116 Cans Coke M123 Bottles Pepsi M124 Chairs Sprite M125 Lights Cola M126 Again, my formula works if I use it on one cell (using CTRL+SHIFT+ENTER), but when I copy to other rows I get the same result as the orginal (copied cell). I know it is an easy solution, but its not working for me. I appreciate any help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
There is nothing wrong with your formula... it is your data.
On Sheet1 you have Units as A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 A/1/160 B/1/160 B/1/160 Whereas on the second sheet you have 1/160 1/160 1/160 1/160 2/160 2/160 3/160 3/160 4/160 4/160 SOATC SOATC SOATC SOATC SOATC ROAE Notice the missing A/ before 1/160? I have inserted A/ in all the rows upto 4/160. The #N/A you see are because there are still no matches. "Scott A" wrote: I did download your sample file. I did the same thing and it worked with my sample build, just not with my actual file. The following link is my actual data file http://wikisend.com/download/540472/Breakout.xls Col A is the column that I want to read two other cells to match. It should look at the values in Col J & L. My ranges are on the other sheet. If you can get it to work I would really appreciate it. Scott "Sheeloo" wrote: Did you download my sample file? Either mail or upload your file so that I can see what is wrong. I simply copied and pasted the data in your post and it worked for me. This is the formula I have =INDEX(AAC, MATCH(B2&C2,Company&Type,0)) "Scott A" wrote: I appreciate the very timely response. I agree and almost understand everything with this formula. While I am no expert, I am no beginner either. This makes it even harder to understand why this isn't working with my actual data. I am still getting #N/A (which I failed to mention in the original post). I have tried CTRL-C & CTRL-V, dragging the bottom right corner, and CTRL-C and Paste Special Formula. All without luck. This absolutely boggles my mind. As there a particular cell format required (i.e., text, general, etc)? I've also to see if there was an option that needed to be enabled or disabled. I have no clue wha tthe problem is. I appreciate your help. "Sheeloo" wrote: How are you copying? I entered everything the way you described with the array formula in A2. Then I copied A2 to A3:A5 and it worked. Download the sample file from http://wikisend.com/download/553030/Sample.xls You can go to A3 and press CTRL-D or Drag the handle from A2 down to A3:A5 or Copy A2 and paste over A3:A5 Are you selecting the formula and then pasting it as text in other cells? "Scott A" wrote: I know the fix is easy, but I can't seem to copy my array formula. In sum, I am trying to have a column check two other columns. Based on the returned values, I expect my value. Following is sample data: AAC Company Type Coke M113 Pepsi M114 Sprite M115 Cola M116 For instance, if it matches "Coke" and M113, then AAC should have "Widget". For Pepsi and M114, then AAC might be "Stuff", etc. On another tab, I have range names to capture the return value. The formula (which works on a single cell) I have in Col A (AAC) is {=INDEX(AAC, MATCH(B2&C2,Company&Type,0))} On the other tab, I have the lookup values (with range names). See following: AAC Company Type Widget Coke M113 Stuff Pepsi M114 More Stuff Sprite M115 Books Cola M116 Cans Coke M123 Bottles Pepsi M124 Chairs Sprite M125 Lights Cola M126 Again, my formula works if I use it on one cell (using CTRL+SHIFT+ENTER), but when I copy to other rows I get the same result as the orginal (copied cell). I know it is an easy solution, but its not working for me. I appreciate any help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
Not sure whether I pasted the link...
Here it is, just in case I did not - http://wikisend.com/download/566064/Breakout.xls |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy array formulas
Sorry for my delayed response. I see what you did. Agree that it works.
Part of my problem is linking to another users XLS which includes the apha-prefix, but as you know does not show on the list. I have since edited your formula to include parsing text so I now capture what you helped me with and do not violate the integrity of the other users data. Thank you for all your help. It works great. "Sheeloo" wrote: Not sure whether I pasted the link... Here it is, just in case I did not - http://wikisend.com/download/566064/Breakout.xls |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when to use array formulas | Excel Discussion (Misc queries) | |||
Array Formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Array formulas | Excel Worksheet Functions | |||
Cannot copy or move array entered formulas ... shared workbk | Excel Worksheet Functions |