dragging formula works for columns but not rows?

[Meenie]

I have excel 2003
I have a worksheet that is linked to two other worksheets.
I use this formula to bring in the info to my worksheet: ='C:\Documents and
Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and
Falls Audits\Chart Audits\[February 08 MS & CC Chart Audit
Compliance.xls]compliance '!$C$5
The worksheet info I was linking to was set up in columns. So I wanted to
change the month from February to March, then drag the formula so the Column
reference (C,D,E,F...) changed but the row number did not.
So I changed the formula in the first cell to ='C:\Documents and
Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and
Falls Audits\Chart Audits\[March 08 MS & CC Chart Audit
Compliance.xls]compliance '!C$5, Then when I dragged it across on my
worksheet, it changed appropriately to ....... '!D$5, 'E$5, etc.
Great That worked.
But the last three items in my worksheet were linked to a different sheet
that is set up in rows.
The formula is: ='[February 08 Falls Compliance.xls]Compliance Question
1'!$C$14
and I want the row numbers to change, not the column.
I changed the formula in the first cell to read ='[March 08 Falls
Compliance.xls]Compliance Question 1'!$C14
(removed the $ before the row reference 14) But when I drag it, it just
copies it instead of changing the row from 14, to 15, 16, etc.
Why doesn't removing that $ work like it did in the other set?
I know it's me, just don't know what's wrong.
I tried looking in help but it just shows removing the $, which is what I
did.
Can anyone help?
Thanks, Meenie

 

[Fred Smith]

Using addresses with $ does work properly in Excel.

When you dragged the '$C14' formula, did you drag it to a different column,
or a different row? You need to drag it to a different row in order for the
address to change.

I suspect your problem is the 'different sheet which is set up in rows'. It
sounds like your data needs to be transposed before your formula is going to
work.

My other suggestion is to mask the file names you are posting. Posting the
acutal file names gives out too much personal information. Use a generic
names like Sheet1 and Sheet2. It will also make your posts much easier to
read.

Regards,
Fred.

 

[Meenie]

Thanks Fred,
I'll was afraid it was something about the way they were set up. I'll try
that.
and thanks for your suggestion re: the addresses. I'll do that next time
Meenie

Using addresses with $ does work properly in Excel.

When you dragged the '$C14' formula, did you drag it to a different column,
or a different row? You need to drag it to a different row in order for the
address to change.

I suspect your problem is the 'different sheet which is set up in rows'. It
sounds like your data needs to be transposed before your formula is going to
work.

My other suggestion is to mask the file names you are posting. Posting the
acutal file names gives out too much personal information. Use a generic
names like Sheet1 and Sheet2. It will also make your posts much easier to
read.

Regards,
Fred.

I have excel 2003
I have a worksheet that is linked to two other worksheets.
I use this formula to bring in the info to my worksheet: ='C:\Documents
and
Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and
Falls Audits\Chart Audits\[February 08 MS & CC Chart Audit
Compliance.xls]compliance '!$C$5
The worksheet info I was linking to was set up in columns. So I wanted to
change the month from February to March, then drag the formula so the
Column
reference (C,D,E,F...) changed but the row number did not.
So I changed the formula in the first cell to ='C:\Documents and
Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir Michelle W\Chart and
Falls Audits\Chart Audits\[March 08 MS & CC Chart Audit
Compliance.xls]compliance '!C$5, Then when I dragged it across on my
worksheet, it changed appropriately to ....... '!D$5, 'E$5, etc.
Great That worked.
But the last three items in my worksheet were linked to a different sheet
that is set up in rows.
The formula is: ='[February 08 Falls Compliance.xls]Compliance Question
1'!$C$14
and I want the row numbers to change, not the column.
I changed the formula in the first cell to read ='[March 08 Falls
Compliance.xls]Compliance Question 1'!$C14
(removed the $ before the row reference 14) But when I drag it, it just
copies it instead of changing the row from 14, to 15, 16, etc.
Why doesn't removing that $ work like it did in the other set?
I know it's me, just don't know what's wrong.
I tried looking in help but it just shows removing the $, which is what I
did.
Can anyone help?
Thanks, Meenie