0

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.

7
  • Do you want to use OFFSET() or create a group of other Named Ranges ? May 1, 2016 at 17:59
  • I have modified the main question for clarity after Gary's Student comment.
    – Rsc Rsc
    May 1, 2016 at 18:07
  • just write in C2 =Sum(A2:B2) and drag it down and leave your first row like you said
    – user555689
    May 1, 2016 at 18:17
  • SuperSam - that is a good answer and will be a backup solution. Why backup? For this to work, I would need to: create C1 without named range ie type out the RC references out, then fill c2, c3 etc and then add named range to C1. But this is a simple example and I need to do this for multiple cells across multiple tabs etc. So a simpler way would be to create the named ranges and build C1 formula with named ranges (much easier for complex formulas with nested IF's and so on) and then just fill. With your solution, I still have a hairy problem of writing out the C1 formula in a complex way.
    – Rsc Rsc
    May 1, 2016 at 18:35
  • Define Name amounts with relative references remove the $ sign from the range reference
    – user555689
    May 1, 2016 at 20:46

2 Answers 2

1

Define Name amounts with relative references in Refers to write =Sheet1!AL136:AN136
When you define name it will add automatically the $ sign like this =Sheet1!$AL$136:$AN$136 it is a fixed range with absolute references it will not change when you move the formula, you have to remove the $ sign in the Define Name, so when you write amounts in your formula and you drag it down it will change accordingly

1
  • Yes, it works. Thanks a lot. Quite obvious too now that you showed how to do it - just change the references that the range refers to. Fantastic!
    – Rsc Rsc
    May 2, 2016 at 22:22
0

Here is how you would use the Name. In C1 enter:

=SUM(OFFSET(amounts,ROW()-1,0))

and copy down:

enter image description here

The Nameless way would be to enter =SUM(A2:B2) in C2 and copy down.

1
  • OFFSET does work for the simple example but how would you make it work for the formula example that I have now added to the question?
    – Rsc Rsc
    May 1, 2016 at 20:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .