# Max Number of Cell References Work Around?

1. ## 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.

2. ## 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. ## 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?

4. ## Re: Max Number of Cell References Work Around?

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

5. ## 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

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