+ Reply to Thread
Results 1 to 5 of 5

Max Number of Cell References Work Around?

  1. #1
    Registered User
    Join Date
    07-06-2021
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2019
    Posts
    18

    Max Number of Cell References Work Around?

    Hi,
    I have a really long formula which makes reference to over 600 cells, which is above the 255 cell reference limit.
    However, I'm not sure how to get around this by simplifying or splitting up the formula, without losing the embedded IF statements functionality.
    I've attached an example workbook to show the layout that I am working with.
    The formula I need help with is entered into column AF, with the desired outcome of outputting the second chronological combination of items.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Max Number of Cell References Work Around?

    A better explanation of what you are trying to do AND sample expected outcomes are needed here.

  3. #3
    Registered User
    Join Date
    07-06-2021
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2019
    Posts
    18

    Re: Max Number of Cell References Work Around?

    Hi,
    I have re-attached the example workbook, edited to hopefully demonstrate a bit better what I am after. in the final two columns (AF and AG) of the table, I need to have formulas that will pull the first and second chronological combinations of items based off their start and end dates. The first combination is limited to a maximum of three items, and the second combination is limited to a maximum of 6 items, however the number of possible combinations is obviously much greater than that for the first combination, as it is dependent on what the first combination of items was, and also has more variables.
    The way we have written this formula using the IF and CONCAT functions has over 600 cell references in which exceeds the excel limit, so our second combination formula does not work.
    the desires outcome is now demonstrated in the first combination formula column where the item names are outputted based on their start dates.
    I hope this makes a bit more sense?
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Max Number of Cell References Work Around?

    I did not see any sample (manual) output ( in AF and AG) in your file.

  5. #5
    Registered User
    Join Date
    07-06-2021
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2019
    Posts
    18

    Re: Max Number of Cell References Work Around?

    Hi,
    I have added in some manual outputs into the Second Combination (AG) column, with explanations behind them in the next column (AH).
    AF should still have the working formula outputting the first combination?
    Thank you
    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. [SOLVED] Named cell references don't work in one sheet only
    By Ziza in forum Excel General
    Replies: 4
    Last Post: 05-18-2019, 08:17 AM
  2. [SOLVED] Formula to read cell references as number value
    By kosherboy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-03-2014, 09:15 AM
  3. [SOLVED] Counting The number of Identification References in a cell
    By newtoexcel24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2013, 02:57 AM
  4. Formula to shift cell references a certain number of columns not working
    By amartin575 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-15-2013, 01:35 PM
  5. Auto generate a work order number that references a file on computer
    By Omnib0mb3r in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2011, 12:36 PM
  6. Replies: 1
    Last Post: 10-29-2009, 06:17 AM
  7. Replies: 0
    Last Post: 09-27-2006, 02:05 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