Workbook reference and incrementing cell location by 4

[canblue]

<!--[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
[TABLE="class: MsoNormalTable, width: 394"]
<tbody>[TR]
[TD="width: 70"]

9-Jul-14​

[/TD]
[TD="width: 76"]

9-Oct-14​

[/TD]
[TD="width: 52"]

Oct​

[/TD]
[TD="width: 58"]

537​

[/TD]
[TD="width: 52"]

45.18​

[/TD]
[TD="width: 52"]

68.85​

[/TD]
[TD="width: 56"]

17.97​

[/TD]
[TD="width: 64"]

172.32​

[/TD]
[TD="width: 46"]

136​

[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Oct-14​

[/TD]
[TD="width: 76"]

9-Jan-15​

[/TD]
[TD="width: 52"]

Jan​

[/TD]
[TD="width: 58"]

583​

[/TD]
[TD="width: 52"]
[/TD]
[TD="width: 52"]
[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 64"]
[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Jan-15​

[/TD]
[TD="width: 76"]

9-Apr-15​

[/TD]
[TD="width: 52"]

Apr​

[/TD]
[TD="width: 58"]

628​

[/TD]
[TD="width: 52"]

47.58​

[/TD]
[TD="width: 52"]

72.13​

[/TD]
[TD="width: 56"]

18.14​

[/TD]
[TD="width: 64"]

181.03​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Apr-15​

[/TD]
[TD="width: 76"]

9-Jul-15​

[/TD]
[TD="width: 52"]

Jul​

[/TD]
[TD="width: 58"]

673​

[/TD]
[TD="width: 52"]

47.58​

[/TD]
[TD="width: 52"]

72.13​

[/TD]
[TD="width: 56"]

18.84​

[/TD]
[TD="width: 64"]

181.03​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Jul-15​

[/TD]
[TD="width: 76"]

9-Oct-15​

[/TD]
[TD="width: 52"]

Oct​

[/TD]
[TD="width: 58"]

729​

[/TD]
[TD="width: 52"]

47.58​

[/TD]
[TD="width: 52"]

89.77​

[/TD]
[TD="width: 56"]

18.84​

[/TD]
[TD="width: 64"]

209.05​

[/TD]
[TD="width: 46"]

192​

[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Oct-15​

[/TD]
[TD="width: 76"]

9-Jan-16​

[/TD]
[TD="width: 52"]

Jan​

[/TD]
[TD="width: 58"]

777​

[/TD]
[TD="width: 52"]

47.82​

[/TD]
[TD="width: 52"]

77.25​

[/TD]
[TD="width: 56"]

18.91​

[/TD]
[TD="width: 64"]

189.52​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Jan-16​

[/TD]
[TD="width: 76"]

9-Apr-16​

[/TD]
[TD="width: 52"]

Apr​

[/TD]
[TD="width: 58"]

826​

[/TD]
[TD="width: 52"]

50.25​

[/TD]
[TD="width: 52"]

80.35​

[/TD]
[TD="width: 56"]

19.68​

[/TD]
[TD="width: 64"]

198.14​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Apr-16​

[/TD]
[TD="width: 76"]

9-Jul-16​

[/TD]
[TD="width: 52"]

Jul​

[/TD]
[TD="width: 58"]

921​

[/TD]
[TD="width: 52"]

50.25​

[/TD]
[TD="width: 52"]

160.7​

[/TD]
[TD="width: 56"]

19.68​

[/TD]
[TD="width: 64"]

326.34​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Jul-16​

[/TD]
[TD="width: 76"]

9-Oct-16​

[/TD]
[TD="width: 52"]

Oct​

[/TD]
[TD="width: 58"]

990​

[/TD]
[TD="width: 52"]

50.25​

[/TD]
[TD="width: 52"]

115.51​

[/TD]
[TD="width: 56"]

19.68​

[/TD]
[TD="width: 64"]

254.23​

[/TD]
[TD="width: 46"]

213​

[/TD]
[/TR]
[TR]
[TD="width: 70"]

10-Sep-16​

[/TD]
[TD="width: 76"]

9-Jan-17​

[/TD]
[TD="width: 52"]

Jan​

[/TD]
[TD="width: 58"]

1040​

[/TD]
[TD="width: 52"]

50.47​

[/TD]
[TD="width: 52"]

83.87​

[/TD]
[TD="width: 56"]

19.72​

[/TD]
[TD="width: 64"]

204.12​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Jan-17​

[/TD]
[TD="width: 76"]

9-Apr-17​

[/TD]
[TD="width: 52"]

Apr​

[/TD]
[TD="width: 58"]

1086​

[/TD]
[TD="width: 52"]

52.71​

[/TD]
[TD="width: 52"]

78.75​

[/TD]
[TD="width: 56"]

20.13​

[/TD]
[TD="width: 64"]

199.66​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Apr-17​

[/TD]
[TD="width: 76"]

9-Jul-17​

[/TD]
[TD="width: 52"]

Jul​

[/TD]
[TD="width: 58"]

1146​

[/TD]
[TD="width: 52"]

52.71​

[/TD]
[TD="width: 52"]

102.72​

[/TD]
[TD="width: 56"]

20.13​

[/TD]
[TD="width: 64"]

238.26​

[/TD]
[TD="width: 46"]
[/TD]
[/TR]
[TR]
[TD="width: 70"]

9-Jul-17​

[/TD]
[TD="width: 76"]

9-Oct-17​

[/TD]
[TD="width: 52"]

Oct​

[/TD]
[TD="width: 58"]

1202​

[/TD]
[TD="width: 52"]

52.71​

[/TD]
[TD="width: 52"]

95.87​

[/TD]
[TD="width: 56"]

20.13​

[/TD]
[TD="width: 64"]

227.23​

[/TD]
[TD="width: 46"]

162​

[/TD]
[/TR]
</tbody>[/TABLE]


This is what I want to end up with on another workbook to use as input for a graph.

YENumbers

A B
[TABLE="class: MsoNormalTable, width: 60"]
<tbody>[TR]
[TD="width: 36"]

14​

[/TD]
[TD="width: 45"]

136​

[/TD]
[/TR]
[TR]
[TD="width: 36"]

15​

[/TD]
[TD="width: 45"]

192​

[/TD]
[/TR]
[TR]
[TD="width: 36"]

16​

[/TD]
[TD="width: 45"]

213​

[/TD]
[/TR]
[TR]
[TD="width: 36"]

17​

[/TD]
[TD="width: 45"]

162​

[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[arthurbr]

Supposing your source data begins in row 1 and the row where you extract it to is also row1 try =INDEX(UsageDetails!$I$1:$I$100,(row()-1)*4+1)) and pull down as needed

You'll have to adapt if starting row is different ( the INDIRECT function is volatile so try to avoid it)

 

[canblue]

Supposing your source data begins in row 1 and the row where you extract it to is also row1 try =INDEX(UsageDetails!$I$1:$I$100,(row()-1)*4+1)) and pull down as needed

You'll have to adapt if starting row is different ( the INDIRECT function is volatile so try to avoid it)

Thank you for your reply. Success. I can get the formula to work perfectly when I was experimenting with it in the same workbook. If I move the working expression to the location that I want, it does not work properly. I do not understand all of the background processing. When I evaluate the formula the ROW result is different. I am in need of some additional help to understand. I can provide offline any info required. Just let me know where to send it.

Thanks,
Bryan

 

[arthurbr]

You can provide any information here on the forum so that all members can help you.
What goes wrong?

 

[canblue]

You can provide any information here on the forum so that all members can help you.
What goes wrong?

Oops, my error. While trying to document my problem, some of the fog cleared. If I start with the input table reference location of 1 output is bad. If I set the starting row reference a 4, things all work out. What I could not "see" was that a "temp" table was built and the index referenced that table. Once I tried to provide you with more details and run additional evaluations, little pieces started to fall into place. I did spend a lot of time yesterday experimenting, not making much headway.

You have solved my problem, the solution for it I NEVER would have arrived at.

Thanks. You were bang on and I just needed to start at the right row. I guess that it is never too late to learn new things or admit that I do not know it all.

This site rocks.

Best regards,
Bryan

 

[arthurbr]

You're welcome