+ Reply to Thread
Results 1 to 6 of 6

Formula to expand/retract Table B according to entry actions on Table A

  1. #1
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    126

    Formula to expand/retract Table B according to entry actions on Table A

    I wish to replicate column C from a table (e.g. LogTable on the attached file) dynamically to column Q on a second table (e.g. AveTable).

    I want to make sure the link is live between the two, and that if I delete or add rows to the LogTable, AveTable automatically expands/retracts to represent the same number of entries.

    I most desire the use of a formula to make this happen. Is this possible?
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,055

    Re: Formula to expand/retract Table B according to entry actions on Table A

    Convert Q:S to range and
    put this formula in Q2

    =CHOOSE({1,2,3},toTable[Date],IF(toTable[Present]="No",NA(),MMULT(--(toTable[[Dur1]:[Dur10]]>0),SEQUENCE(10,,,0))),IF(toTable[Present]="No",NA(),MMULT(N(+toTable[[Dur1]:[Dur10]]),SEQUENCE(10,,,0))/MMULT(--(toTable[[Dur1]:[Dur10]]>0),SEQUENCE(10,,,0))))
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    126

    Re: Formula to expand/retract Table B according to entry actions on Table A

    Bo_Ry, thank you for your suggestion.

    I am puzzled. The formula did work but then stopped working. I did delete a few rows from the toTable and reentered data. That may have contributed to the malfunction? (see attached file) Not sure if this formula is reliable.

    Also, I realized that columns R and S need to fill in automatically too.
    Attached Files Attached Files

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,055

    Re: Formula to expand/retract Table B according to entry actions on Table A

    =CHOOSE({1,2,3},LogTable[Date],IF(LogTable[Present]="No",NA(),MMULT(--(LogTable[[Dur1]:[Dur10]]>0),SEQUENCE(10,,,0))),IF(LogTable[Present]="No",NA(),MMULT(N(+LogTable[[Dur1]:[Dur10]]),SEQUENCE(10,,,0))/MMULT(--(LogTable[[Dur1]:[Dur10]]>0),SEQUENCE(10,,,0))))

    This formula is for call Dynamic array spill available with Microsoft 365 with monthly channel update.
    only put formula at Q2 it will spill to S2 and spill down. No need to put formula at Q3:Qxx or R2:Sxx


    https://support.microsoft.com/en-us/...1-87a7eb36e531
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    126

    Re: Formula to expand/retract Table B according to entry actions on Table A

    This isn't working for me.

    Any time I enter data inside the table that spill formula becomes corrupted. Same when I try to add a new date in column C.

  6. #6
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    126

    Re: Formula to expand/retract Table B according to entry actions on Table A

    Maybe I didn't make myself clear. On the attached file. I only desire for cell Q20 to automatically populate with the same date as cell C20, which is 1/19/17. In other words, anytime a new date is entered in Column C, then that same date is entered in the same row in Column Q. That way, then the formulas in columns R and S will activate.

    I'd like to stay away from the use of spilled arrays if at all possible. This workbook may be used with other versions of excel. Perhaps a macro will be necessary in this case but would prefer a formula if at all possible.
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 04-25-2020, 09:46 AM
  2. [SOLVED] Code to move data from an entry table to a historical table by date.
    By rlh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2014, 11:04 AM
  3. Formula to expand headings to table columns
    By LoftySuth in forum Excel General
    Replies: 3
    Last Post: 01-25-2014, 10:24 PM
  4. Expand a table by adding rows ( table feature 2007 and later)
    By Pepe Le Mokko in forum Excel General
    Replies: 3
    Last Post: 08-13-2013, 05:12 AM
  5. Need help: two columns and a table of actions
    By Labellavita in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-18-2013, 07:34 PM
  6. Macro to Group/Ungroup a specific Table plus macro to expand/Collapse grouped table.
    By Michell Feitosa in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-04-2012, 08:10 PM
  7. Collapse/Expand - Pivot table Fields - Need equivalent option in Excel VBA Pivot table
    By ragavendraph in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 03:00 PM

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