+ Reply to Thread
Results 1 to 6 of 6

Formula to repeat column headings to a new column...

  1. 11-26-2019, 10:11 AM #1
    LesleyB ATL
    LesleyB ATL is offline
    Registered User
    Join Date
    10-31-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Formula to repeat column headings to a new column...

    Hello

    I have a worksheet providing defined lists to another part of my workbook; the column headers are the range names and I need to repeat them in column A so they can appear in a drop down list.

    I have a simple formula in Column A of =B1, = C1, = D1 etc going down the column. However, I cant drag/copy the formula down as it changes the row number, not the column number. I cant set the formula up horizontally and then transpose it, as it then changes the cell references its looking for and creates an error.

    I really don't want to have to manually add the formula for each cell in column A... initially it is not too bad, but over time the number of columns will increase quite dramatically and I would like to start off with at least 200 rows ready with the formula!

    Any suggestions? (this is probably easy-peasy if you know how, but I don't! )

    The attached screenshot shows what happens when I try to drag the formula down

    Snip.jpg

    Many thanks!
    Reply With QuoteRegister To Reply
  2. 11-26-2019, 10:17 AM #2
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Formula to repeat column headings to a new column...

    Hi. You can use:

    =OFFSET($B$1,,ROWS($1:1)-1,,)
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 11-26-2019, 10:18 AM #3
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Formula to repeat column headings to a new column...

    or:
    =INDEX($B$1:$T$1,ROWS($1:1))

    change the red T to whatever you need.
    Reply With QuoteRegister To Reply
  4. 11-26-2019, 10:20 AM #4
    LesleyB ATL
    LesleyB ATL is offline
    Registered User
    Join Date
    10-31-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Talking Re: Formula to repeat column headings to a new column...

    Perfect! You are a genius Glenn!
    Many thanks
    Reply With QuoteRegister To Reply
  5. 11-26-2019, 10:23 AM #5
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Formula to repeat column headings to a new column...

    Nope. just done it before!!

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Reply With QuoteRegister To Reply
  6. 11-26-2019, 10:27 AM #6
    LesleyB ATL
    LesleyB ATL is offline
    Registered User
    Join Date
    10-31-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Formula to repeat column headings to a new column...

    Done and Done
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Consecutive repeat of column headings based on cell values
    By mikear82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2018, 08:49 AM
  2. Repeat formula in column H until the last row with data in column G
    By matteobsuetti in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2015, 08:36 AM
  3. Copy column sub-headings to inserted column via formula
    By Geppstar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-13-2013, 05:36 PM
  4. [SOLVED] VBA to detect next empty cell in column and add formula, then repeat for next column, ...
    By Marijke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2013, 07:28 AM
  5. How to repeat column headings on sheet 2
    By wlarson in forum Excel General
    Replies: 1
    Last Post: 12-31-2007, 04:31 PM
  6. What is formula to repeat row at top when column headings are numb
    By kcasillas in forum Excel General
    Replies: 1
    Last Post: 11-09-2005, 09:20 PM
  7. How do I repeat column headings on all of my sheets w/o cut/paste
    By Momof3boys in forum Excel General
    Replies: 1
    Last Post: 09-16-2005, 12:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1