+ Reply to Thread
Results 1 to 5 of 5

Concatenate formula with a reference to a table

  1. #1
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Concatenate formula with a reference to a table

    Hello,

    Wondering if someone could help me out with a quick solution?

    Here's the original formula: =-SUM(INDEX(SUMIFS(Table4300[Amount],Table4300[Restriction],INDEX(CATBOARD2[item1]:CATBOARD2[item29],MATCH([@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))-SUM(INDEX(SUMIFS(Table4310[Amount],Table4310[Restriction],INDEX(CATBOARD2[item1]:CATBOARD2[item29],MATCH([@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))

    So I need a part of this formula to be dynamic as I may add columns to my table through the year.

    The part I need help with: CATBOARD2[item1]:CATBOARD2[item29]

    So the first part of this will be fine as item 1 will always be the first count. However, item29 may go up as columns are added. So if I added three columns, the third column added would be item32.

    For this to be dynamic I was trying to use the functions concatenate/& and counta for the headers (=CATBOARD2[#Headers]-2) and subtract the first the two columns (col 1 is for a match, and col2 is reference somewhere else) as those are not needed to get my count of items there are [itemXX].

    XX of the item above is what I would like to be dynamic.

    If anyone could help, it would be greatly appreciated. If the file is needed please let me know.

    Thanks,
    Ant

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Concatenate formula with a reference to a table

    Try using the INDIRECT function and some text manipulation to get your CATBOARD2[item1]:CATBOARD2[itemxxxx] to the value you want.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Re: Concatenate formula with a reference to a table

    Would you happen to know how to add the indirect function into this formula?

    I have only used it a few times, and it's been very basic to like directing it to a sheet. I feel like I am close but cannot get it to work.

    This is what I have now trying to implement the indirect function:

    =-SUM(INDEX(SUMIFS(Table4300[Amount],Table4300[Restriction],INDEX(INDIRECT("CATBOARD2[item1]:CATBOARD2[item"&COUNTA(CATBOARD2[#Headers])-2&"]"),MATCH(Summary_tbl[@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))-SUM(INDEX(SUMIFS(Table4310[Amount],Table4310[Restriction],INDEX(INDIRECT("CATBOARD2[item1]:CATBOARD2[item"&COUNTA(CATBOARD2[#Headers])-2&"]"),MATCH(Summary_tbl[@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))

    When I evaluate the formula, it brings out the correct references but then #REF out and does not return anything.

    Please let me know if I should provide the file.

    Thanks,
    Ant
    Attached Images Attached Images

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Concatenate formula with a reference to a table

    Well, I just did a quick test on my own, and this works:
    =INDEX(INDIRECT("Table2[[item1]:[item30]]"),2,3)

    Note that I only mention the table name once not twice (i.e. not "Table2[[item1]:Table2[item30]]"), which is what it seems like you are doing. But, it appears that is what you had in your original formula (which is what I referenced in my original response)... Does it work in your original formula? It doesn't when I change mine to match yours.

  5. #5
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Re: Concatenate formula with a reference to a table

    Nice PauleyB! This works perfectly!

    As you mentioned, not mentioning the table name for the second time during the index did the trick as long as adding extra brackets to the items to correctly grab the range needed.

    The final formula used:

    =-SUM(INDEX(SUMIFS(Table4300[Amount],Table4300[Restriction],INDEX(INDIRECT("CATBOARD2[[item1]:[item"&COUNTA(CATBOARD2[#Headers])-2&"]]"),MATCH(Summary_tbl[@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))-SUM(INDEX(SUMIFS(Table4310[Amount],Table4310[Restriction],INDEX(INDIRECT("CATBOARD2[[item1]:[item"&COUNTA(CATBOARD2[#Headers])-2&"]]"),MATCH(Summary_tbl[@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))

    Thanks for your time and effort,
    Ant

+ 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. Use CONCATENATE to create a reference formula
    By hjbarazi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2019, 11:16 AM
  2. [SOLVED] To create Table 2 with reference to Table 1 ... Which formula should I enter?
    By chief_abound in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-21-2018, 01:18 AM
  3. [SOLVED] Can VlookUp function use a concatenate formula in the table array area?
    By greywarden in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2017, 07:46 AM
  4. Concatenate in a formula to reference a different worksheet
    By buyshirts in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 12-17-2015, 12:06 PM
  5. Replies: 0
    Last Post: 09-30-2015, 04:50 PM
  6. A formula to reference a table
    By jttwnsnd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-05-2014, 03:27 AM
  7. Replies: 3
    Last Post: 11-29-2013, 05:33 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