Absolute Reference Question


New Member
Jan 15, 2005
Hi everyone, first time poster.

I was wondering if someone with a little more excel knowledge could be able to help me out with an excel file I am working on.

Basically I need to propgate a formula over 12 months (colums), and over 2000 different rows. The problem arises because I do not want to absolute reference the formula over the 2000 rows, just the 12 months.

The only way I can think of doing this is by propogating the formula down over the 2000 rows, then adding the absolute reference manually, then propogating it over the 12 months. This is very time consuming because I have two seperate cells that need to be absolute referenced in each row.

Is there any way that I can make excel absolute reference the same portion of the formula over the entire 2000 rows, or am I going to be forced to manually add the reference to each individual formula?

I hope this makes sense, it barely does to me and I may not have clearly expressed my problem.

Any help would be greatly appreciated, Thanks!

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Absolute Reference is quite often thought to be $A$1, however, if you select the cell reference and then select F4, you would find that you can have $A1, A$1 as well.

It sounds to me as if you should enter the formula across the 12 months and then select all 12 months and copy down the 200 rows; in this case there would be no need for any absolute reference.

HTH (y)
Upvote 0
Post your formula, explain in a bit of detail what it is meant to do and we can tell you what you need. :)
Upvote 0
Yeah, I usually use F4 and I've noticed that too, What is the difference between $A1, $A$1 and A$1? I always use $A$1

Also, if I propgate the formula over the 12 months first, then absolute reference it, then try to propopgate it down over the 2000 rows, I end up with incorrect formulas

for example, I need:

A1/$0$1 B1/$O$1 C1/$0$1
A2/$0$2 B2/$0$2 C2/$0$2


The way I understand it is that I cant progpate it both ways with an absolute reference because it wont change.
Upvote 0
Maybe i should explain a bit more what I need to do.

I have to figure out the usage percentages by month for about 2000 different different accounts.

Im dividing the month by the total for each account and while its easy to do it for 12 months, I do not want to have to do it for each account.

The problem gets worse in that I have to multiply the percentage of each month by the 2005 budgeted amounts in order to get a usage estimate for 2005 by month.

Thanks again for any assistance! :)
Upvote 0
The $ sign signifies an absolute reference. If you have one before the column reference the column is "locked". If you have one before thr row reference the row number is "locked"

e.g. $A$1 when filled either down or across will remain at $A$1

A$1 when copied across will become B$1, C$1 etc
but when copied down will stay at A$1 (as the row is locked)

$A1 will change when filled down but not across, as this time the Column is locked
Upvote 0
MAybe you already know this - I'll post it anyway

All Colums are in Letters (A,B,C,D,E,F.......)
All Rows are in Numbers (1,2,3,4,5,......)

Cells are referenced A1, A2.. B1, B2.. .etc...

The $ sign is used to "Lock" the value it precedes.

$A1 Will lock the Column A if you propagate it below.
A$1 will lock the Row 1 if you propagate it
$A$1 will lock both Column A and Row 1 (i.e. Cell A1) if you propagate.

Maybe this example will help:
Row 1 and Column A are numbers I put in. All other cells are fromulas with and without the $ and combinations thereof.

Upvote 0
From what I can see,

=A1/$O1 =B1/$O1 =C1/$O1

would probably do the trick. You can just copy those down and they will automatically change the 1 to 2, 2 to 3 etc for the row references.

You can probably actually get away with just =A1/$O1 and then paste that to the entire table
Upvote 0
Yeah, the absolute reference $A1 worked out well, I just propogated it down, then across. Saved me about 100 hours!

Thanks again.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back