1

I have a special condition. I have two variables in my formula.

=A1+B1

Now, when I drag it vertically I want to change the value of A and on dragging horizontally I want to change the value of B. I don't want to change column. Ever.
For example:
Sample image

How do I do it?

3
  • I think more information is needed. What kinds of values are you dealing with? If they are simply reference values you may be able to achieve that with formulas, but since "dragging" is a cursor (or mouse input) function you'll probably have to address this with a VBA script. To my knowledge there aren't any "formulas" or functions built into Excel that deal with mouse input logic. Commented May 22, 2017 at 17:48
  • @kyle_engineer : the values change in dragging vertically.....And applying the $ I can freeze them as well...But how do I do it for just one variable?! Commented May 22, 2017 at 18:17
  • You just want A1+B1 or do you have another formula using it? can you give more details?
    – yass
    Commented May 22, 2017 at 18:21

2 Answers 2

2

Use this one formula:

=INDEX($A:$A,COLUMN(A:A))+$B1

It will adjust as you asked in both directions.

2
  • Actually, my formula is: COUNTIF(A1:F1,B1) where values of A and F need to change vertically and B changes horizontally.... Commented May 23, 2017 at 9:55
  • It's funny how the answer to my new problem derives from both of your answers.: =COUNTIF(INDEX($A:$A,COLUMN(B:B)):INDEX($F:$F,COLUMN(B:B)),$B1) Commented May 23, 2017 at 11:55
1

You can use absolute references for A1 in the first column:
=$A$1+B1
and drag it down it will be:
=$A$1+B2 in the next row and B3 in the next ...

For the second column use:
=$A$2+B1
and drag it down

Another method is by using indirect:
=SUM(INDIRECT("A"&COLUMN(A1)),INDIRECT("B"&ROW(A1)))
and you can drag it Horizontally and vertically

1
  • One note: INDIRECT is volatile and will recompute any time Excel recomputes regardless if the underlying data has changed or not. INDEX is not volatile and will only recompute if the data changes. A full sheet of INDIRECT will slow the re-compute time of Excel. Commented May 22, 2017 at 18:41

You must log in to answer this question.

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