Good Day Everyone,
This is my first post and I would ask that if I have made any posting errors that someone would be so kind as to point them out to me. Although I have read all of the rules and guidelines, I may have missed something.
On to my problem. I am using Excel 2010 on Windows 7 Enterprise and trying to develop named ranges for the sheets in a workbook that will allow only the cells with values in them to be displayed in a chart. I have a shared workbook on our network that I cannot use VBA on because the owners what the extension to remain xlsx. With that said, I have a chart with named ranges that sort of works where each sheet's named range is like the one below.
=OFFSET(Initiator!$M$2,0,0,COUNTIF(Initiator!$L:$L,"<>")-1,1)
The problem is that in this instance the chart displays the values for Cells M2, M3, M4, M5 and M8, with two blank spaces between M5 and M8, along with two blank spaces after M8. As can be seen by the data below there are values in Cells M15, M18, M19 and M21. But, these are not displayed in the chart. All of the cells in Column M have a formula in them producing the values shown.
I have found the perfect solution in a You Tube demonstration at https://www.youtube.com/watch?v=JA2r_9RjLCs. The resultant array formula that I thought should work is:
{=IF(COUNT($M$2:$M$20)>=ROWS(M$2:M2),INDEX($J$2:$J$20,MATCH(SMALL(IF(LEN($M$2:$M$20)>0,ROW($M$2:$M$20),""),ROWS(M$2:M20)),ROW($M$2:$M$20),0),1),"")}
Unfortunately, I get a #NUM! error in all of the cells that I try to apply it to. My INDEX and MATCH knowledge is week, at best, even after countless hours of reviewing their usage and trying to apply it to my problem.
I have found a couple of posts on Mr. Excel and a couple elsewhere that were close to helping, but fell short for one reason or another in allowing me to resolve this issue. If anyone can help me find a solution to this problem with a little explanation on how their formula is put together, I would be most appreciative.
Thank You in Advance,
GoodMD
Excel 2010 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
[TH]
[TH]
[TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD="bgcolor: #E4EDF8"]CR#[/TD]
[TD="bgcolor: #E4EDF8"]Date/Time Received[/TD]
[TD="bgcolor: #E4EDF8"]Date/Time Completed[/TD]
[TD="bgcolor: #E4EDF8"]Time Elapsed[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC16-0500-001[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0008-001[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0009-001[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0019-001[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0020-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0021-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0021-002[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0022-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0023-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0026-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0026-002[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0027-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0028-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0030-001[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0031-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0032-001[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0033-001[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0034-001[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0034-002[/TD]
[TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD]SC17-0034-003[/TD]
[TD]
[TD]
[TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Initiator[/TD]
[/TR]
</tbody>[/TABLE]
This is my first post and I would ask that if I have made any posting errors that someone would be so kind as to point them out to me. Although I have read all of the rules and guidelines, I may have missed something.
On to my problem. I am using Excel 2010 on Windows 7 Enterprise and trying to develop named ranges for the sheets in a workbook that will allow only the cells with values in them to be displayed in a chart. I have a shared workbook on our network that I cannot use VBA on because the owners what the extension to remain xlsx. With that said, I have a chart with named ranges that sort of works where each sheet's named range is like the one below.
=OFFSET(Initiator!$M$2,0,0,COUNTIF(Initiator!$L:$L,"<>")-1,1)
The problem is that in this instance the chart displays the values for Cells M2, M3, M4, M5 and M8, with two blank spaces between M5 and M8, along with two blank spaces after M8. As can be seen by the data below there are values in Cells M15, M18, M19 and M21. But, these are not displayed in the chart. All of the cells in Column M have a formula in them producing the values shown.
I have found the perfect solution in a You Tube demonstration at https://www.youtube.com/watch?v=JA2r_9RjLCs. The resultant array formula that I thought should work is:
{=IF(COUNT($M$2:$M$20)>=ROWS(M$2:M2),INDEX($J$2:$J$20,MATCH(SMALL(IF(LEN($M$2:$M$20)>0,ROW($M$2:$M$20),""),ROWS(M$2:M20)),ROW($M$2:$M$20),0),1),"")}
Unfortunately, I get a #NUM! error in all of the cells that I try to apply it to. My INDEX and MATCH knowledge is week, at best, even after countless hours of reviewing their usage and trying to apply it to my problem.
I have found a couple of posts on Mr. Excel and a couple elsewhere that were close to helping, but fell short for one reason or another in allowing me to resolve this issue. If anyone can help me find a solution to this problem with a little explanation on how their formula is put together, I would be most appreciative.
Thank You in Advance,
GoodMD
Excel 2010 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
J
[/TH][TH]
K
[/TH][TH]
L
[/TH][TH]
M
[/TH][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1
[/TD][TD="bgcolor: #E4EDF8"]CR#[/TD]
[TD="bgcolor: #E4EDF8"]Date/Time Received[/TD]
[TD="bgcolor: #E4EDF8"]Date/Time Completed[/TD]
[TD="bgcolor: #E4EDF8"]Time Elapsed[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2
[/TD][TD]SC16-0500-001[/TD]
[TD]
10/13/16 3:00 PM
[/TD][TD]
10/13/16 23:00
[/TD][TD]
8.0
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3
[/TD][TD]SC17-0008-001[/TD]
[TD]
1/4/17 12:31 PM
[/TD][TD]
1/6/17 7:00
[/TD][TD]
42.5
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4
[/TD][TD]SC17-0009-001[/TD]
[TD]
1/4/17 12:31 PM
[/TD][TD]
1/6/17 7:00
[/TD][TD]
42.5
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5
[/TD][TD]SC17-0019-001[/TD]
[TD]
1/6/17 1:06 PM
[/TD][TD]
1/9/17 11:00
[/TD][TD]
21.9
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6
[/TD][TD]SC17-0020-001[/TD]
[TD]
1/6/17 2:05 PM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7
[/TD][TD]SC17-0021-001[/TD]
[TD]
1/9/17 12:08 PM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8
[/TD][TD]SC17-0021-002[/TD]
[TD]
1/9/17 12:16 PM
[/TD][TD]
1/9/17 13:00
[/TD][TD]
0.7
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9
[/TD][TD]SC17-0022-001[/TD]
[TD]
1/9/17 1:38 PM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10
[/TD][TD]SC17-0023-001[/TD]
[TD]
1/9/17 1:39 PM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
11
[/TD][TD]SC17-0026-001[/TD]
[TD]
1/10/17 8:32 AM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
12
[/TD][TD]SC17-0026-002[/TD]
[TD]
1/10/17 8:35 AM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
13
[/TD][TD]SC17-0027-001[/TD]
[TD]
1/10/17 8:47 AM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
14
[/TD][TD]SC17-0028-001[/TD]
[TD]
1/10/17 9:08 AM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
15
[/TD][TD]SC17-0030-001[/TD]
[TD]
1/10/17 10:26 AM
[/TD][TD]
1/10/17 11:15
[/TD][TD]
0.8
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
16
[/TD][TD]SC17-0031-001[/TD]
[TD]
1/10/17 1:30 PM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
17
[/TD][TD]SC17-0032-001[/TD]
[TD]
1/11/17 7:46 AM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
18
[/TD][TD]SC17-0033-001[/TD]
[TD]
1/11/17 7:52 AM
[/TD][TD]
1/11/17 10:15
[/TD][TD]
2.4
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
19
[/TD][TD]SC17-0034-001[/TD]
[TD]
1/11/17 7:58 AM
[/TD][TD]
1/11/17 10:15
[/TD][TD]
2.3
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
20
[/TD][TD]SC17-0034-002[/TD]
[TD]
1/11/17 7:58 AM
[/TD][TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
21
[/TD][TD]SC17-0034-003[/TD]
[TD]
1/11/17 7:58 AM
[/TD][TD]
1/11/17 10:15
[/TD][TD]
2.3
[/TD][/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
22
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
23
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
24
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Initiator[/TD]
[/TR]
</tbody>[/TABLE]
