I want to do the following:
for the first row in Excel, create a formula with named range. say: A1=1, B1=3. Create named range A1:B1 called "amounts". cell C1=sum(amounts). this makes row1 very readable.
now row1 is one of many rows with data in col A and col B. So I want to fill the formula from C1 into C2, C3...C1000 and so on. So if A2=4, B2=5, I want C2=9. If I try to fill, cell C2 will also have =sum(amounts) so the value in C2 will be 4 not 9.
How to fix this?
What I want is this - I want the first row to be readable so if I want to understand what the formulas really mean, I can click on the cell C1 in the first row and get that info due to very readable named ranges (instead of R1C1 references). But for C2, C3...C1000 etc, those need not be named ranges or anything and for these even R1C1 references may be ok because I will never click on them to understand them. Only requirement for C2,C3... is that they should have correct formula on fill. For understanding the formula, I can always click on first row.
Hope this is clear but if it is not I will edit for clarity if needed :)
I am adding an example of formula complexity -
=IF( $BE136 = a_value,
IF(LARGE(AL136:AN136, 1) > 1,MATCH( LARGE(AL136:AN136, 1), AL136:AN136, 0), 0 ),
BF135 )
How much better if I could do this in this way -
AL136:AN136 is range "amts", $BE136 is range "test_criteria", BF135 is "alt_val"
= IF( test_criteria = a_value,
if( large(amts, 1) > 1, match(large(amts, 1), amts, 0), 0 ),
alt_val )
After writing such a thing in row1, I want to copy this to c2, c3, etc.
As I understand my own problem better, I also want to avoid having to carefully type the formula in RC format in the first place.
Thanks for your help.