Excel Vlookup / IF / Textbefore

ChrisRolando 

OK, Chris. I too began in the olden days. My first "spreadsheet" was created in 1970 or 1971 by me, using APL, quite literally "A Programming Language,"--a software package developed for and primarily used by mathematicians and scientists. It had the capability of creating multidimensional arrays of numbers, with each cell being addressable. I never used VisiCalc per se, but did make use of a lot of the early spreadsheets, graduated ultimately to Lotus at work, Quattro at home, and ultimately Excel in both locales. I've been retired for a bit over 20 years now, am in my early 80s, have fun helping people resolve issues with Excel. I'm by no means the most accomplished user of Excel--do not use VBA, preferring to figure out how to get from A to B using some of the amazing functions that Excel makes available. I do consider myself to be well informed in databases, database design, the use of tables, and--in general--good reliable design of a spreadsheet.

That was the basis on which I offered the suggestion that you concentrate on redesigning your worksheet because if it were to be used more than once, it's going to be a long term headache for its users.

Now that you've said it's a one-time use sheet, and that your challenge is

to copy the formula(s) that sit in cells L8, L16, L25, L26, L28 and paste them (or in some other way copy them) into L53, L55, L63, L67 and L77. The reason that this continues to fail is that the row numbers under the Parent row vary in their displacement to the row above,

I can see why total re-design isn't appropriate. Given the hints in your paragraph above, I wonder if INDIRECT, with row numbers calculated for those varying displacements--based on yet another set of "helper columns" that show the displacement appropriate for the row-- incorporated in the resulting formulas.

Frankly, though, this sentence

This is caused by other calculations going on in hidden columns and on feeder and helper sheets.

worries me. With all those other calculations going on, in all those places, well, that's why, without actually seeing what you're working with--and I get it, that it can't be shared--but it's very very difficult to offer any substantive and specific suggestions. INDIRECT can be used creatively to modify formulas and references made within formulas IF you know what those modifications need to be. I gather you need to do this not merely on row L, though, but on many more of the 2800 lines in the spreadsheet, and unless there's a fixed and known pattern to those varying displacements, you are working with a monster that is (not telling you anything you don't know) too wild to tame.