Autofill/size adjacent column next to a table

[Will85]

I have a table that is getting its data from an Odata connection to our accounting system. In my excel workbook, I can feed filters to that table that a user can manipulate to select the year, account, dept, etc. etc.

As the user selects those filters, the table rows grow or shrink automatically.

One of the columns contains a url formatted so that I can wrap it in the excel hyperlink formula, and its clickable, it does not come through as clickable in the table.

My table is in columns A:C, the url in the table is in column C, my hyperlink formula is in column D. Is there a way to setup column D so that it also grows or shrinks as the table does automatically.

Another example is that in Column B of my table, is a code, the code equates to a state, I have an index match in column E taking that code and finding the two letter abbreviation based on another tab in the workbook called states. I too would want this to grow and shrink as the table does.

 

[plettieri]

Hello Will85

To achieve the functionality where columns D and E grow or shrink automatically as the table in columns A:C changes, you can use Excel's structured references and formulas within an Excel Table. This way, the formulas will automatically adjust as rows are added or removed from the table.

Here's how you can set it up:

1. Convert Your Data Range to a Table:
   • Select the range A:C that contains your data.
   • Go to the Insert tab and click on Table.
   • Ensure the "Create Table" dialog box has the "My table has headers" checkbox checked and click OK.
2. Add Formulas to the Table:
   • Click on any cell in the table to activate the Table Tools Design tab.
   • Add a new column for your hyperlink formula:
     • In cell D2 (assuming your table starts in row 2), enter the formula:
       excel
       
       =HYPERLINK([@[URL Column]], "Click Here")
       Replace URL Column with the actual header name of your URL column.
   • Add a new column for your state abbreviation:
     • In cell E2, enter the formula:
       excel
       
       =IFERROR(INDEX(States!$B$2:$B$50, MATCH([@[Code Column]], States!$A$2:$A$50, 0)), "")
       Replace Code Column with the actual header name of your code column, and adjust the ranges in the INDEX and MATCH functions to match your "States" tab.
3. Extend the Table:
   • When you add these formulas, Excel will automatically extend the table to include columns D and E.
   • The formulas will automatically fill down the entire column and adjust as rows are added or removed from the table.

Example:​

Assume your table headers are in row 1, and the data starts from row 2. Your table headers might look like this:

• A1: Year
• B1: Code
• C1: URL

In cell D2, you would enter:

=HYPERLINK([@], "Click Here") In cell E2, you w...ed functionality. Hope this helps plettieri