+ Reply to Thread
Results 1 to 5 of 5

How to have user entry defining table column headings

  1. #1
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    How to have user entry defining table column headings

    I am trying to just create a very simple cell reference formula. I need some of my table headers to be up to user input.

    In the first user-defined heading, I entered in ='Setup Page'!H19 in the header column, so that it would return the information the user typed into cell H19, but instead it just returns a 0. In the second one, I pointed it to cell I19, but it just returned 02 and erased the formula.

    How do I get my table headings to reflect what the user enters in on the setup page?

    This is the setup page where user puts in the names of the interaction headings and create the dropdown menus. (dropdown menus work)
    setup info.jpg

    Here is the table, and as you can see the headings are 0, 02, 03 rather than what the user entered in. The 0 should say "Follow-up email"; the 02 should say "Review given" etc.
    table.jpg
    Last edited by dsrt16; 03-19-2018 at 03:44 PM.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: How to have user entry defining table column headings

    1. Set Header Row to Off

    .
    HeaderRowSetToOff.jpg
    .

    2. Create your formula in row above the data

    .
    FormulaNowWorks.jpg
    .

    3. If referring to table data use this notation

    .
    FormulaInTableCells.jpg
    Last edited by kev_; 03-17-2018 at 06:34 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: How to have user entry defining table column headings

    Is there not a way, even using VBA, to rename column headers? I want them to stay headers because I have an entire table with a lot of formulas in them referring to column names. These last 7 columns of the table don't have formulas referencing them, but the other ones do, and if I got rid of the table headers it would mean a lot of work.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: How to have user entry defining table column headings

    Yes you can rename the columns either manually or by VBA
    - what Excel does not allow is formulas in table headers

    Test in attached workbook
    - (for illustration) original headers are in row 16, new headers are in row 19 (of Setup Page)
    - formula in table column I is =[@[Orig C]]&[@[Orig D]]&[@[Orig E]]
    - run vba with {CTRL}k
    - formula automatically amended to =[@[Follow-up email]]&[@[Review given]]&[@[Added to portfolio]]
    - put old headers back with {CTRL}{SHIFT}k

    run with {CTRL} k
    Please Login or Register  to view this content.
    run with {CTRL}{SHIFT} k
    Please Login or Register  to view this content.
    If the values are in adjacent table columns then you can use method in 1st macro, otherwise use method in 2nd macro
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: How to have user entry defining table column headings

    Awesome. Thank you!

+ 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. How to make a Templates column headings dynamic with the Pivot table headings
    By DashBD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-20-2017, 05:20 PM
  2. Replies: 8
    Last Post: 06-29-2016, 04:07 PM
  3. [SOLVED] Defining the column of a named table to use with Match
    By j_Southern in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-04-2013, 11:13 AM
  4. user box options if cell entry is a duplicate found in a table column
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-24-2009, 02:04 PM
  5. Locate table column headings in row
    By johncob in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2009, 09:45 AM
  6. [SOLVED] Can I invert a table so row headings are now column headings etc
    By Sharon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2005, 04:06 PM
  7. [SOLVED] pivot table row vs column headings
    By mrs.champ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2005, 07:06 PM

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