Absolute Reference Question

[Michaelwhli]

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!

 

[Whisperer14]

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

 

[KenWright]

Post your formula, explain in a bit of detail what it is meant to do and we can tell you what you need.

 

[Michaelwhli]

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

etc...

The way I understand it is that I cant progpate it both ways with an absolute reference because it wont change.

 

[Michaelwhli]

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!

 

[GorD]

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

 

[dimbasco]

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.

Book1
	A	B	C	D	E	F
1		1	2	3	4	5
2	10	10	20	30	40	50
3	20	200	200	60	800	100
4	30	6000	2000	90	24000	150
5	40	240000	20000	120	960000	200
6	50	12000000	200000	150	48000000	250
7	60	720000000	2000000	180	2880000000	300
Sheet1

Dimbasco

 

[Michaelwhli]

Wow, I didnt realize that. It totally works now. I really appreciate the help, thanks!

 

[KenWright]

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

 

[Michaelwhli]

Yeah, the absolute reference $A1 worked out well, I just propogated it down, then across. Saved me about 100 hours!

Thanks again.