Excel Questions Thread

[Racer]

When a copying a formula left to right, how do you make it so that

=SUMIFS(Data!$F:$F,Data!$B:$B,$A3,Data!$D:$D,A3)

goes to

=SUMIFS(Data!$F:$F,Data!$B:$B,$A3,Data!$D:$D,A4)

If I do $A3 or $A$3, the part in bold stays at A3 when copying from left to right. If I do A$3 or A3 the part in bold goes to B3, C3, D3, etc. when copying from left to right. Everything else is working how I want it to.

I could do it manually in a 2 or 3 minutes but it's for an Excel VBA class I'm taking so I have to write a Macro that does it. Thanks.

[Bobble]

Until someone smarter comes along....

I'd do it with OFFSET and COLUMNS functions (offset from the starting cell by the value from the current column) -- that wouldn't really be a macro, though.

[CU Tiger]

$A3 should work...since it doesn't, I'd probably do 2 cells highlight them both and bucket handle it to the right.

[SteveMax58]

Yeah, what Bobble said is the only way you can do it with a formula.

Dragging right increments Columns ONLY, as dragging down increments Rows ONLY (pending the use of $ to say "no, dont increment that").

[Racer]

How would I adjust those formulas using the offset function? (I do have everything else in my macro working properly now. My first legit macro ever. :))

[Racer]

I just ended up writing a loop that recreated the numbers on a separate sheet in a manner where I could get the formula to calculate properly.