+ Reply to Thread
Results 1 to 5 of 5

Weird interaction between tables across multiple worksheets.

  1. #1
    Registered User
    Join Date
    03-09-2017
    Location
    Perth, Australia
    MS-Off Ver
    Office 365
    Posts
    4

    Weird interaction between tables across multiple worksheets.

    Hi all,

    I'm developing some complex spreadsheets for the office. One of the requirements is that a user will want to link a value in a table from one page to a master sheet (or from a table that calculates the value in a different way or a manual entry).

    However if the tables across both worksheets share the same row designation it will come up as a table link (T_General[(at symbol)[Cost $]])

    This is okay for me, I can correct it and look out for it. But I guarantee that others in the office won't pick up on it and it could have some pretty serious consequences.

    I can't use VBA in this spreadsheet either unfortunately.

    Anyway to turn this off?

    Regards,
    Ben

    PS. Apparently I have "links or videos or images in this message" I don't? - It's the at symbol. Admins should probably look into that.
    Attached Files Attached Files
    Last edited by gibus; 03-08-2020 at 11:24 PM. Reason: Weird forum bug

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Weird interaction between tables across multiple worksheets.

    Can you attach a sample workbook? Look at the yellow banner at the top of the page.

  3. #3
    Registered User
    Join Date
    03-09-2017
    Location
    Perth, Australia
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Weird interaction between tables across multiple worksheets.

    Attached a sample in original post as mentioned.
    Thanks,

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Weird interaction between tables across multiple worksheets.

    Have you found a solution for this yet? I was wondering if you could use Index/Match for this. For instance, in cell D7 on the "Table 1 Sheet", you could enter:
    =INDEX(Table_2[Automatic Calulations],MATCH([@Item],Table_2[Item],0))

    Or, if the answer will be on one sheet or the other, maybe this?
    =IFERROR(INDEX(Table_2[Automatic Calulations],MATCH([@Item],Table_2[Item],0)),INDEX(Table_2_Alt[Different Calulcation],MATCH([@Item],Table_2_Alt[Item],0)))

  5. #5
    Registered User
    Join Date
    03-09-2017
    Location
    Perth, Australia
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Weird interaction between tables across multiple worksheets.

    Not a solution exactly, but I have a workaround.
    would still like to know if anyone knows any better.

    Workaround was to move the entire sheet ~ 1000 cells down the page and hide cells 1-999

    The problem is more about how end users will enter values into this cell when we're rushing to get a tender out. Automatic values are used where I can but sometimes a user will just want to override the value.

    Thanks for the suggestions.
    Ben

+ 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. Search Multiple tables on multiple worksheets - find next/ previous item.
    By Icesurf3r in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2019, 09:25 AM
  2. Replies: 1
    Last Post: 10-30-2018, 07:38 PM
  3. Replies: 0
    Last Post: 05-16-2017, 09:36 AM
  4. Code adding rows to multiple tables in multiple worksheets
    By gabriel78 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2017, 05:26 AM
  5. Consolidating Multiple Tables from Multiple Worksheets onto one Worksheet????
    By JB20 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2015, 12:55 PM
  6. Applying filters to multiple pivot tables across multiple worksheets
    By gbarragan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2010, 12:05 PM
  7. [SOLVED] Creating Tables From Multiple Worksheets
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-03-2006, 05:20 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