Absolute Referencing

[bullcreel]

I have 'bat calcs'!$BA64 in cell BY17. I am trying to copy/paste into cell FA17 and then it should read 'bat calcs'!$BA65, with the absolute referencing, however, it is staying 'bat calcs'!$BA64.
Thanks in advance.

 

[Joe4]

If you are copying your original formula, found in row 17, to another column in the same row, the row portion of your formula won't change (why would it)?
If you copied a formula in row 17 to a cell in row 18, then the row reference in your original formula of row 64 would change to 65.

If you are trying to copy the formula across the same row, but have the row numbers in the formulas change, you would probably need to use an OFFSET function.
But you would have to explain exactly the logic (i.e. jump one row every 30 columns, etc).

 

[bullcreel]

OK...thanks, I will look into using an offset function.

 

[Joe4]

OK. If you need assistance figuring it out, please post back here, providing your logic of how the row number should be calculated.

 

[bullcreel]

I just need the BA to stay the same and the 64 to increment by 1 all the time. So as I copy to different spots it goes...

BA64
BA65
BA66
etc.

Thanks

 

[Joe4]

That is really not the logic I am looking for. Excel does not increment like that. You need a formula you can code to.
Is there any rhyme or reason to the ranges you are copying the formula to?

For example, column BY is the 77th column, and column FA is the 157th column.
In your original explanation, you were copying from the 77th column to the 157th column, a jump of 80 columns.
So, are you always going to be moving 80 columns across to past the formula (i.e. the next one will be the 237th column, or cell IC17)?

If so, you could use this formula in BY17 and copy to FA17 and then to IC17, etc:

=OFFSET('bat calcs'!$BA64,((COLUMN()+3)/80)-1,0)

 

[bullcreel]

I will be copying to (BY17, FA17, IC17, BY39, FA39, IC39) on one sheet. There are 6 other sheets I will be doing the same, but the BA increment will still go up by 1 each time as I progress thru the 6 other sheets.
I hope that makes sense.

 

[Joe4]

As I see it you have four options:
1. Enter all the formulas manually
2. Copy the formulas, and then edit them to show the correct range
3. Incorporate the Offset formula I showed you
4. Write VBA to populate the formulas for you via a loop, according to your logic (this is what you would have to use if you want Excel to "remember" the row number from the last one it copied).

 

[bullcreel]

Thanks. what I have been doing is copy the formulas then edit them.
The formula worked until I copied to cell BY39. I was thinking it was going to do that.

Thanks for all your help.

 

[Joe4]

Yeah, your copying process doesn't follow typical logic one would expect.
It sounds like your logic is that for every 80 columns to the right you copy over, you want to increase the row number by 1, and not change the column reference.
That isn't any typical logic one would expect.

Typically, if you have not locked your ranges down, Excel would increase the row number of the formula by the same number of rows you are shifting (i.e. if you were copying from row 1 to row 4, it would increase the row number in your formula by 3 since 4-1=3). If you are copying within the same row, it would not change the row reference in your formula (since you aren't moving off of that row).

And it the same logic follows for columns when copying formulas.

The range/column references of the "last cell you copied to" mean nothing to Excel. It doesn't keep an internal "counter" of the row or column references.
It simply looks at where you are copying from and where you are copying to.