+ Reply to Thread
Results 1 to 11 of 11

Filling cells on a sheet absolute referencing a table on another sheet

  1. 04-23-2020, 03:15 AM #1
    jitterbug888
    jitterbug888 is offline
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Filling cells on a sheet absolute referencing a table on another sheet

    Hello,
    Ive named my table and the columns, in the tab called "data"..

    I`m trying to create a simple dashboard on a separate sheet summing and counting the following columns

    here I want to total the sales and gst. and add them together in the 3rd row.
    then count the total invoices.

    The "Services" are listed in one column as 4 different services, wanted to count the subtotals for all 4...

    Screenshot_20200423170614.png

    I have separate columns for year, month and day in the table, and want to count them here.

    Screenshot_20200423170549.png

    spreadsheet attached.

    my attempt at absolute referencing seems to fail..

    Screenshot_20200423165756.png
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-23-2020, 03:26 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    Please add some expected results manually to your example. Thanks
    Reply With QuoteRegister To Reply
  3. 04-23-2020, 03:54 AM #3
    jitterbug888
    jitterbug888 is offline
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    Thanks Pepe,

    Screenshot_20200423175337.png
    Reply With QuoteRegister To Reply
  4. 04-23-2020, 03:57 AM #4
    AliGW
    AliGW is online now
    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
    80,916

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    Inaccurate sample answers are of no use to anyone - we need to see the results you are expecting. Add a few manually CALCULATED ones to the workbook and post it again, please.
    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.
    Reply With QuoteRegister To Reply
  5. 04-23-2020, 04:00 AM #5
    AliGW
    AliGW is online now
    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
    80,916

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    It looks as if all you need are a few SUMIFS and COUNTIFS:

    https://support.office.com/en-gb/art...6-611cebce642b

    https://support.office.com/en-gb/art...c-aa8c2a866842
    Reply With QuoteRegister To Reply
  6. 04-23-2020, 04:13 AM #6
    jitterbug888
    jitterbug888 is offline
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    Its pretty much accurate, ive fixed the dollar amounts summed, I think the visual I uploaded previous post explains which columns I want summed or counted from the "data" tab. I tried to use absolute referencing for example as shown in my initial post above and I think its an accurate formula but its not calculating. The counts I refer to in my diagram are just that, counting how many of each day, month or year are in the named columns.

    I wanted to do this using absolute referencing to my named table in the Data sheet. Thanks for the links, I need to figure out how to use the sumif and countif within an absolute reference, and hopefully get my absRef links working in this sheet.

    Attachment 674002
    Last edited by jitterbug888; 04-23-2020 at 04:20 AM.
    Reply With QuoteRegister To Reply
  7. 04-25-2020, 06:35 AM #7
    jitterbug888
    jitterbug888 is offline
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Lightbulb Re: Filling cells on a sheet STRUCTURED referencing a table on another sheet

    I managed to create the Structured references, and get them working, however one of them is not calcualting dont know why, returns zero.

    I misnamed this post, it was supposed to be how to create "STRUCTURED references" to another sheets table.

    On the Dashboard tab, the Service "Crown" is named,the formula is consistent with the other 3 above it, but there is no count occuring,

    Attachment 674380

    In the data tab, there are 3 Crowns "Crown"... In the Service Column...

    Attachment 674381

    sheet attached...

    I believe I have named the Value correctly and inputted it into the formula correctly.

    WeChat Screenshot_20200425203041.png
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-25-2020, 06:47 AM #8
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    There are extra space after Crown in Data
    =COUNTIF(buildingAnatomy2[Service],"Crown ")
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  9. 04-25-2020, 08:14 AM #9
    jitterbug888
    jitterbug888 is offline
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    Hi Bo,
    my new sheet has no spaces, neither in the data column, but its still returning zero,, I`ve also checked the uploaded sheet, I cannot see that space...

    Attachment 674398
    Reply With QuoteRegister To Reply
  10. 04-25-2020, 11:39 AM #10
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Filling cells on a sheet absolute referencing a table on another sheet

    I see the space and =len(J15) return 6 while "Crown" only has 5 characters

    Untitled.jpg
    Reply With QuoteRegister To Reply
  11. 04-25-2020, 06:43 PM #11
    jitterbug888
    jitterbug888 is offline
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Thumbs up Re: Filling cells on a sheet absolute referencing a table on another sheet

    ah awesome I see that now and have corrected the spacing issue with backspace in the formula bar,
    thanks for that didnt know of the LEN filter
    cheers thanks Bo!!
    Last edited by jitterbug888; 04-25-2020 at 06:50 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How can I make absolute reference only cells that referenced from another sheet??
    By marins0330 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2017, 09:01 AM
  2. [SOLVED] Referencing Table on Another Sheet
    By StormStrikes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-26-2014, 04:18 AM
  3. Referencing a table to another sheet based on a specific table value
    By happy5 in forum Excel General
    Replies: 8
    Last Post: 09-16-2013, 11:01 PM
  4. referencing of table value to another sheet
    By Dewy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-04-2010, 10:03 AM
  5. Filling empty cells with the right data from another sheet
    By sentinela in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2009, 05:56 PM
  6. adding content to a table by filling a sheet
    By Borgas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2006, 07:50 PM
  7. Non-Static table/sheet referencing
    By Ken B in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-07-2006, 03:36 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1