Need some advice on working with long formulas. The particulars of my formula aren't important, but here it is for reference:
=IFERROR(IF(AND('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$17,'.OPS01-MXL'!$F$2=CONCATENATE('ACES UPLOAD'!X2,'ACES UPLOAD'!Y2)),'.OPS01-MXL'!$F$53,IF('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$18,0,IF('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$19,0,INDEX('CLIN ALLOCATIONS'!$E$20:$BB$23,MATCH('.OPS01-MXL'!$F$1,'CLIN ALLOCATIONS'!B20:B23,0),MATCH(CONCATENATE('ACES UPLOAD'!X2,'ACES UPLOAD'!Y2),'CLIN ALLOCATIONS'!$E$12:$BB$12,0)*'.OPS01-MXL'!$G$75)))),0)
The formula is a bit of a convoluted test against values in the [unnamed] range of 'CLIN ALLOCATIONS'!B17 through B23. Based on which value is found, do something, i.e., either set the result to zero, set it to a value found in the cell '.OPS01-MXL'!$F$53, or finally using an INDEX plus horizontal and vertical MATCH functions, multiple the value in '.OPS01-MXL'!$F$53 by a percentage found in the INDEX table.
The issue is I would like to replace all of the absolute tab references to '.OPS01-MXL'!$F$1 with a dynamic reference using values from a nearby column: MATCH(INDIRECT("'" & "." & AJ2 & "'!$F$1" where AJ2 contains ".OPS01-MXL". Sounded simple until I got the error message that I've hit the 255 character limit.
Did I mention I need to populate 68,000 rows with this formula? The tab reference for each row is already there but changes every 350 rows or so, so I'd really like a draggable formula rather than having to manually update it for each new tab reference. Renaming tabs with shorter names is an option, I suppose, but would make the finished workbook less user-friendly than leaving the more descriptive names in place.
Any ideas on how to either shorten the existing formula to a point that will handle the additional text count, or circumvent the limitation altogether?
Thanks,
Tom
Bookmarks