<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
Details workbook input source
A B C D E F G H I
<tbody>
</tbody>
This is what I want to end up with on another workbook to use as input for a graph.
YENumbers
A B
<tbody>
</tbody>
I have been experimenting with ROW and INDIRECT. The problem that I cannot see past
is the fact that for each year the “pointer” must be advanced 4 cells to pickup the next value. I can
get the INDIRECT to work IF I supply the Details worksheet cell number for column B and I. The problem
is that when I copy the cell all of the cell “addresses” are messed up and adding 4 to the cell “address”
does not create the correct result.
YENumbers B1 >>> =Details!$L48
I created a third column in the YENumbers work book that extracted the source cell number and
put that into an INDIRECT formula:
=INDIRECT("UsageDetais!I" & C1)
What I am looking for is a formula that will extract data from Details and stick the result in YENumbers
and it will increment correctly to select the data 4 cells down.
Thanks,
Bryan
Details workbook input source
A B C D E F G H I
9-Jul-14 | 9-Oct-14 | Oct | 537 | 45.18 | 68.85 | 17.97 | 172.32 | 136 |
9-Oct-14 | 9-Jan-15 | Jan | 583 | | | | | |
9-Jan-15 | 9-Apr-15 | Apr | 628 | 47.58 | 72.13 | 18.14 | 181.03 | |
9-Apr-15 | 9-Jul-15 | Jul | 673 | 47.58 | 72.13 | 18.84 | 181.03 | |
9-Jul-15 | 9-Oct-15 | Oct | 729 | 47.58 | 89.77 | 18.84 | 209.05 | 192 |
9-Oct-15 | 9-Jan-16 | Jan | 777 | 47.82 | 77.25 | 18.91 | 189.52 | |
9-Jan-16 | 9-Apr-16 | Apr | 826 | 50.25 | 80.35 | 19.68 | 198.14 | |
9-Apr-16 | 9-Jul-16 | Jul | 921 | 50.25 | 160.7 | 19.68 | 326.34 | |
9-Jul-16 | 9-Oct-16 | Oct | 990 | 50.25 | 115.51 | 19.68 | 254.23 | 213 |
10-Sep-16 | 9-Jan-17 | Jan | 1040 | 50.47 | 83.87 | 19.72 | 204.12 | |
9-Jan-17 | 9-Apr-17 | Apr | 1086 | 52.71 | 78.75 | 20.13 | 199.66 | |
9-Apr-17 | 9-Jul-17 | Jul | 1146 | 52.71 | 102.72 | 20.13 | 238.26 | |
9-Jul-17 | 9-Oct-17 | Oct | 1202 | 52.71 | 95.87 | 20.13 | 227.23 | 162 |
<tbody>
</tbody>
This is what I want to end up with on another workbook to use as input for a graph.
YENumbers
A B
14 | 136 |
15 | 192 |
16 | 213 |
17 | 162 |
<tbody>
</tbody>
I have been experimenting with ROW and INDIRECT. The problem that I cannot see past
is the fact that for each year the “pointer” must be advanced 4 cells to pickup the next value. I can
get the INDIRECT to work IF I supply the Details worksheet cell number for column B and I. The problem
is that when I copy the cell all of the cell “addresses” are messed up and adding 4 to the cell “address”
does not create the correct result.
YENumbers B1 >>> =Details!$L48
I created a third column in the YENumbers work book that extracted the source cell number and
put that into an INDIRECT formula:
=INDIRECT("UsageDetais!I" & C1)
What I am looking for is a formula that will extract data from Details and stick the result in YENumbers
and it will increment correctly to select the data 4 cells down.
Thanks,
Bryan