+ Reply to Thread
Results 1 to 14 of 14

Refer to table name in a formula

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Refer to table name in a formula

    Hello everyone
    I have three tables in three sheets and I would like to put this formula after the table directly at the last column of it

    Please Login or Register  to view this content.
    My issue is how put the string "Total" in the columns AD to AG and in AH put the formula according to the table name

    Untitled.png

    I tried something like that but got an error
    .Range("AH" & p + 1).Formula = "=SUBTOTAL(109," & .ListObjects(1).Name & "[Total])"

    I have posted at this link too
    http://www.eileenslounge.com/viewtopic.php?f=30&t=31606

    Regards
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Refer to table name in a formula

    Hello YasserKhalil,

    My friend, you should know by now not to attach an image or picture, but a sample Workbook, instead. If you could upload a sample Workbook I might be able to assist.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Refer to table name in a formula

    Thanks a lot for reply
    This is a sample .. it is just a table so I didn't attach a file at the first post
    Attached Files Attached Files

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Refer to table name in a formula

    Hello YasserKhalil,

    As we don't know how many Tables you may have on Sheet 1, you may try this sample Code:

    Please Login or Register  to view this content.
    Regards.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Refer to table name in a formula

    In fact this is not the desired as the number of rows in the table is dynamic
    I am trying to loop through three worksheets and each one has a table and each table will be populated with some data.. All is OK till now
    The problem is that I need to insert that formula after each table so I wanted to refer to the table name as in the formula in the first post to make it flexible

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Refer to table name in a formula

    Hi Yasser
    Play around with this...
    Please Login or Register  to view this content.
    Last edited by sintek; 01-12-2019 at 02:54 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Refer to table name in a formula

    Thanks a lot my friend
    The desired 'Total' and the formula should not be part of the table ..and I don't know why the formula causes error .. all what I did is to replace the table name with the
    .ListObjects(1).Name

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Refer to table name in a formula

    Hi Yasser

    Wanting this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Wanting this code...
    Please Login or Register  to view this content.


    The desired 'Total' and the formula should not be part of the table
    Then the total should be placed 2 rows down otherwise it becomes part of the table...
    Try this...

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 01-12-2019 at 09:05 AM.

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Refer to table name in a formula

    Thank you very much my friend. You are very helpful
    Best Regards

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Refer to table name in a formula

    ..................................................
    Thanks.gif

  11. #11
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Refer to table name in a formula

    Hello YasserKhalil,

    I only now see that you awarded me Rep points on this Thread. Thank you, I appreciate it very much!

    Sintek has done a good job, but if you run his code, and then decide to add another line to any table, and run the Code again, "Total" and Count will be duplicated, where the Table had been expanded.

    I have taken the liberty of tweaking Sintek's Code so that you will not have that problem. Please see the attached Sample Workbook below.

    Regards.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Refer to table name in a formula

    Thanks a lot my friend. I could handle this in my original code
    Thank you very much

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Refer to table name in a formula

    @ Winon

    Hallo daar mater...lekker om van jou te hoor...Hoekom so skaars...
    Thank you for the revised code...did not even think of taking that into account...

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Refer to table name in a formula

    You are welcome YasserKhalil, my friend.

    @ sintek,
    Hoekom so skaars...
    The Forum is loaded with so many good Members, that they beat me to a possible solution, before I can respond.
    Keep up your good work!

    Regards.

+ 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. Refer to dynamic table in closed workbook
    By jaryszek in forum Excel General
    Replies: 12
    Last Post: 12-05-2018, 10:51 AM
  2. How to refer to column name from Excel table in formula
    By arbazhamayun in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-27-2018, 08:56 AM
  3. How to refer to Table inside a sheet using ListObjects
    By oliverpa in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-23-2017, 11:23 PM
  4. Refer dynamically to a table named range in VBA
    By freelensia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2017, 02:31 AM
  5. [SOLVED] help with making formula refer to dynamic table
    By juntjoo in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 04-17-2017, 11:25 PM
  6. [SOLVED] Refer to a cell in another column within a table
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-09-2014, 10:00 PM
  7. How to create formula index refer on refference table
    By poojiemilan in forum Excel General
    Replies: 1
    Last Post: 12-31-2011, 07:16 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1