+ Reply to Thread
Results 1 to 8 of 8

Absolute cells in at table

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Absolute cells in at table

    Hello all,
    I've been having difficulty using absolute references in a structured (I think that's the correct term ) table.
    Essentially Criteria range changes as its dragged across and then reverts back to being correct.
    I've attached a very simple example adding a list of units based on a color which I hope demonstrates what I mean.

    Richard.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Absolute cells in at table

    In order to use the columns as absolute, you need to do it like
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Absolute cells in at table

    Here's a workaround (trick) that I was shown a while back:

    =SUMIF(Table1[[#All],[Color]:[Color]],Table2[[#Headers],[Red]],Table1[[#All],[Unit]:[Unit]])
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Re: Absolute cells in at table

    Hello and happy new year Ali and Fluff,
    Well I've sought of got them both to work, but only by adding a third column to the source table , otherwise i still ended up with 8 for Blue and Green.
    But once I'd done this I was able to delete and still get a correct result.
    Ive reattached the workbook and left a sheet named test without the third colums showing incorrct results, if I am leaving out vital comma or colon please let me know,
    Thanks to both of you.

    Richard.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Absolute cells in at table

    Your formula in I6 is looking is looking at the wrong header (it's counting Blue not green). If I drag the formula from G6 across to I6 it works quite happily.

  6. #6
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Re: Absolute cells in at table

    Hi,
    yes, I've tried it again, it this time, I dragged quite slowly and carefully, and you are quite correct this time it has copied across.
    thanks,

    RD.

  7. #7
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Re: Absolute cells in at table

    Hi Ali,
    I've used both replies and both work, but can you explain or point me to where a bit more information about yours, the subtotal
    drop down, can found. I know I've come across it somewhere. Once again, thanks, for your help.
    I'd give a rating but it won't let me.

    Richard.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Absolute cells in at table

    I'm not sure which of these you mean, so I'm providing two links:

    https://support.microsoft.com/en-us/...e-b0a3d4a52b3a

    and:

    https://www.extendoffice.com/documen...total-row.html

    Hope this helps.

    I have offered a solution in your PQ dates column thread - I hope it's what you wanted.

+ 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. [SOLVED] Pull Down Absolute Table Reference
    By RatHat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2020, 04:46 AM
  2. [SOLVED] Filling cells on a sheet absolute referencing a table on another sheet
    By jitterbug888 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-25-2020, 06:43 PM
  3. Anyone know how to make a Table Range absolute?
    By AstToTheRegionalMGR in forum Excel General
    Replies: 7
    Last Post: 03-31-2015, 11:39 AM
  4. [SOLVED] Absolute References in a Table
    By gak67 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2014, 02:27 PM
  5. [SOLVED] Absolute Value in a table (killing me!!!)
    By Dbradford1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-03-2014, 11:16 PM
  6. Replies: 0
    Last Post: 04-08-2013, 05:08 AM
  7. Absolute reference on named table
    By one11 in forum Excel General
    Replies: 2
    Last Post: 10-18-2011, 02:15 PM

Tags for this Thread

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