+ Reply to Thread
Results 1 to 6 of 6

reference to a column of flexible length

  1. #1
    Registered User
    Join Date
    10-26-2011
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    5

    reference to a column of flexible length

    Hey, I have this problem with Excel at work and I have no idea how to solve it. I would really appreciate it if you could at least point me into the right direction.

    So I have an extra sheet with values like
    A1: 2007
    A2: 2008
    A3: 2009

    and then I have another sheet where raw data will be entered, and there should be on column, for every year available in that extra sheet. So, for example, if I added A4: 2010 in the extra sheet, then another column should with '2010' on top should be added to the data sheet.

    If VBA is needed, can you tell me the code?
    Thank you very much for you help!

    Alex

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: reference to a column of flexible length

    Maybe something like this:

    Please Login or Register  to view this content.
    It basically will put whatever is in Column A of Sheet 1 into Row 1 of Sheet 2 once you type it in.

    Hope this helps.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    10-26-2011
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: reference to a column of flexible length

    thank you very much for the fast reply!

    So I guess there's no easier way to do this, I'll have to get a crash course in VBA then.
    and actually that was just the basic thing. the data file is more complex and it should have just as many columns as there are filled cells in column A, sheet 1. do I have to add things like 'insert column' and 'delete column' into the VBA code for that?

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: reference to a column of flexible length

    HI Alex,

    Maybe you can upload a dummy workbook and show me what you want done (before and after) and I can see if I can code it for you. See the attachment for the current code. I have ony inputted years in cells A1:A3. If you add more data (e.g. A4), then the headers will be created in Sheet2.

    abousetta
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-26-2011
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: reference to a column of flexible length

    Ok, so the data sheet should adjust according to the A and B column in the Steering sheet. right now there are 4 years in the B column, so there are 4 colomns each in the data sheet. They aren't linked yet. the goal is, that you just enter the years used once in the steering sheet, and according to how many years there are, as many colomns should exist - each. so also if I delete 2006 in the steering sheet, the data sheet should have just 3 colomns - one for every year. and for every 'block' the same thing.

    I hoped there would be an easy way to do this, but if it's too much coding I will try to find another solution.

    Thanks a lot!

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: reference to a column of flexible length

    Hi Alex,

    I had a look at your workbook and have tried to implement this, but its a bit above my skill level. Keep the thread alive and I'm sure someone with more experience will be able to jump in.

    sorry I couldn't be of more assistance.

    abousetta

    P.S. I have attached the original copy of your xl workbook (Advanced --> Manage Attachments) to this post so others can see the structure
    Attached Files Attached Files
    Last edited by abousetta; 10-27-2011 at 06:27 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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