+ Reply to Thread
Results 1 to 4 of 4

Dynamic reference of table column in VBA

  1. #1
    Registered User
    Join Date
    01-15-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    2016
    Posts
    29

    Dynamic reference of table column in VBA

    Hi,

    My table has 4 columns where the 2nd and 3rd column names are dynamic and their value depends on the value of a particular cell. In column 4, I am trying to write a formula through VBA which references Columns 2 and 3 as below (note that currently the name of column 2 is SP500 and Column 3 is Gold).

    Please Login or Register  to view this content.
    Since Column 2 (SP500) and Column 3 (Gold) can be dynamic, I want to reference them in a way so that I don't have to go and change the column name in the VBA code every time the column name changes. I understand that the Indirect function cannot be used in VBA. Would be great if someone can suggest a solution for this.

    Thanks
    Shashank

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Dynamic reference of table column in VBA

    You could simply skip the structured references and use:

    Please Login or Register  to view this content.
    or you could just extract the names:

    Please Login or Register  to view this content.
    Rory

  3. #3
    Registered User
    Join Date
    01-15-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    2016
    Posts
    29

    Re: Dynamic reference of table column in VBA

    Quote Originally Posted by rorya View Post
    You could simply skip the structured references and use:

    Please Login or Register  to view this content.
    or you could just extract the names:

    Please Login or Register  to view this content.
    Thank you Rory! This is perfect :-)

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Dynamic reference of table column in VBA

    Glad to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Named ranged with reference to second row in dynamic table
    By jaryszek in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2018, 05:18 AM
  2. [SOLVED] Named range and reference to one cell within dynamic table
    By jaryszek in forum Excel General
    Replies: 9
    Last Post: 03-22-2018, 07:04 AM
  3. Dynamic reference to Table Column header
    By aiyathomas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2017, 10:30 PM
  4. Replies: 1
    Last Post: 08-14-2015, 02:49 AM
  5. Pivot table to reference a dynamic range on another sheet
    By Chumpalot in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-30-2014, 09:19 AM
  6. Dynamic reference table from a table source
    By Sailor61 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 10:02 AM
  7. [SOLVED] Create Dynamic Column Lookup Reference based on Table Header
    By jeversf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 12:49 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1