Automation Developer

James Steinbacher

<div>
Thank you so much! &nbsp;That worked great! &nbsp;I'll be reading up on &quot;Index&quot; to understand the solution.
</div>


Thank you so much!  That worked great!  I'll be reading up on "Index" to understand the solution.

<div>
<div class="content wysiwyg-content">
In the following example, which is <b>manually</b> referenced and returns the correct data, Column E contains these formulas: <br />

<ul>
<li>Rows 2 - 7: =SourceData!$A1.</li>
<li>Rows 8 - 13: =SourceData!$A2.</li>
<li>Rows 14 - 19: =SourceData!$A3</li>
</ul>
and so on...<br />
<br />
The Column E data is used to drive VLookup formulas in Column G.<br />
<a href="https://filedb.experts-exchange.com/incoming/2020/04_w15/1449253/ReferencingHelp.PNG" target="_blank" title="ReferencingHelp.PNG (51 KB)"><img alt="ReferencingHelp.PNG" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2020/04_w15/800_1449253/ReferencingHelp.PNG" /></a><br />
When I try to use the auto-fill dot in the lower right of a group of selected cells to fill down, I get the following <b>incorrect</b> references:<br />

<ul>
<li>Rows 2 - 7: =SourceData!$A1.</li>
<li>Rows 8 - 13: =SourceData!$A7.</li>
<li>Rows 14 - 19: =SourceData!$A13</li>
</ul>
<br />
<br />
In this particular case, I need to fill-down over <b>2000</b> of these groups (12,000+ total rows), so manually adjusting the Column E references is not a good option.
</div>
</div>


ReferencingHelp.PNG

In the following example, which is manually referenced and returns the correct data, Column E contains these formulas: 
Rows 2 - 7: =SourceData!$A1.
Rows 8 - 13: =SourceData!$A2.
Rows 14 - 19: =SourceData!$A3

and so on...

The Column E data is used to drive VLookup formulas in Column G.


When I try to use the auto-fill dot in the lower right of a group of selected cells to fill down, I get the following incorrect references:
Rows 2 - 7: =SourceData!$A1.
Rows 8 - 13: =SourceData!$A7.
Rows 14 - 19: =SourceData!$A13



In this particular case, I need to fill-down over 2000 of these groups (12,000+ total rows), so manually adjusting the Column E references is not a good option.

Excel - Problem incrementing cell references by group of rows

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.