I have spreadsheets with lot of external linking. The row references change ever so often. I get the new row references using a match formula. Is there a way for example by highlighting range B2 to B5, the macro would pull the row reference values from D2 to D5 and change the row references in B2 to B5 to equal the values in D2 to D5. Column C is showing the result that I want.
<title>Excel Jeanie HTML</title>Sheet1
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 128px;"> <col style="width: 142px;"> <col style="width: 144px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td> </td> <td style="font-weight: bold;">Change to</td> <td style="font-weight: bold;">Match Row</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">='Cust List'!B1</td> <td style="text-align: right;">='Cust List'!B12</td> <td style="text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">='Cust List'!B2</td> <td style="text-align: right;">='Cust List'!B13</td> <td style="text-align: right;">13</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">='Cust List'!B3</td> <td style="text-align: right;">='Cust List'!B14</td> <td style="text-align: right;">14</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">='Cust List'!B4</td> <td style="text-align: right;">='Cust List'!B15</td> <td style="text-align: right;">15</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B2</td> <td>='Cust List'!B1</td></tr> <tr> <td>C2</td> <td>='Cust List'!B12</td></tr> <tr> <td>B3</td> <td>='Cust List'!B2</td></tr> <tr> <td>C3</td> <td>='Cust List'!B13</td></tr> <tr> <td>B4</td> <td>='Cust List'!B3</td></tr> <tr> <td>C4</td> <td>='Cust List'!B14</td></tr> <tr> <td>B5</td> <td>='Cust List'!B4</td></tr> <tr> <td>C5</td> <td>='Cust List'!B15</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web >> Excel Jeanie HTML 4
<title>Excel Jeanie HTML</title>Sheet1
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 128px;"> <col style="width: 142px;"> <col style="width: 144px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td> </td> <td style="font-weight: bold;">Change to</td> <td style="font-weight: bold;">Match Row</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">='Cust List'!B1</td> <td style="text-align: right;">='Cust List'!B12</td> <td style="text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">='Cust List'!B2</td> <td style="text-align: right;">='Cust List'!B13</td> <td style="text-align: right;">13</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">='Cust List'!B3</td> <td style="text-align: right;">='Cust List'!B14</td> <td style="text-align: right;">14</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">='Cust List'!B4</td> <td style="text-align: right;">='Cust List'!B15</td> <td style="text-align: right;">15</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B2</td> <td>='Cust List'!B1</td></tr> <tr> <td>C2</td> <td>='Cust List'!B12</td></tr> <tr> <td>B3</td> <td>='Cust List'!B2</td></tr> <tr> <td>C3</td> <td>='Cust List'!B13</td></tr> <tr> <td>B4</td> <td>='Cust List'!B3</td></tr> <tr> <td>C4</td> <td>='Cust List'!B14</td></tr> <tr> <td>B5</td> <td>='Cust List'!B4</td></tr> <tr> <td>C5</td> <td>='Cust List'!B15</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web >> Excel Jeanie HTML 4