Creating flexible tables with update options

  • Hello everyone,


    I have a worksheet with dozens of fiscal weeks aligned either side by side and one under the other, leaving one or two row(s) and column(s). You can see it in the attachment file. They are aligned as if they are tables but they are not. I applied some SUMIFS and COUNTIFS to get values from another sheet in the same workbook.
    Now, here is the problem: I need to add another team or value to those tables. Is there any way to do this? Is it possible to apply something like "named range tables". When I added another team name, this should have been also updated in another "tables".


    This solution would really be great and helpful for me and probably any other people on this forum.


    Thank you in advance.

  • Re: Creating flexible tables with update options


    Not sure if I have understood it right..You mean to say that you will enter Value is FW1 column and rest of the Columns should populate automatically(Like Value1, Value2..)?

  • Re: Creating flexible tables with update options


    Quote from mohammedismail;773240

    Not sure if I have understood it right..You mean to say that you will enter Value is FW1 column and rest of the Columns should populate automatically(Like Value1, Value2..)?


    Hello Ismail,


    Yes, we are almost at the same point. I'll add a new team to somewhere (FW1 can be) and this will be populated in the other tables so that i won't need to enter update them manually that would take my time.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Re: Creating flexible tables with update options


    You really would be better off using built-in excel tables, that is why they are available.


    Then a simple bit of code will update all the tables when a button is clicked. Your formulas will automatically fill down to the new row in each table. The tables can be placed anywhere on the sheet and the code will work for any number of tables.


    Try he attached file.


    The code is:

  • Re: Creating flexible tables with update options



    It is pretty cool, Kj! Thank you very much. Just a little question, can we add an action to add a column with the header such as Value 3 or Value N, anywhere in the table(s), if possible.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Re: Creating flexible tables with update options


    Try the attached. I have added buttons to add a new column, remove a column and also to remove a team.


    In future when replying to a post please do not quote the entire post to which you are replying, it makes the entire thread long and less easy to follow. If you need to quote a particular portion of a post then by all means include that portion quoted in your reply. Thanks

  • Re: Creating flexible tables with update options


    works like a charm! Thank you very much, Kj. I also apologize for "reply with quote".

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Re: Creating flexible tables with update options


    You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!