Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I just don't get it please help relative reference question
Tomkat743" wrote:
=SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3)) I don't understand why the A3 won't change to A4 when I drag this formula down I need to populate 100 cells in a column and don't want to have to change it manually. As you can see I have tried both the $A3 and just A3 but nothing changes. Please help me understand. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I just don't get it please help relative reference question
Bothe of those should change when you drag them down as both have a relative
row number. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tomkat743" wrote in message ... Tomkat743" wrote: =SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3)) I don't understand why the A3 won't change to A4 when I drag this formula down I need to populate 100 cells in a column and don't want to have to change it manually. As you can see I have tried both the $A3 and just A3 but nothing changes. Please help me understand. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I just don't get it please help relative reference question
I BELIEVE IT SHOULD BUT IT DOESN'T!!!!
I can't figure it out could it be a setting in the new 2007 office? I've changed everything I can think of but thank you for the feedback. "Bob Phillips" wrote: Bothe of those should change when you drag them down as both have a relative row number. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tomkat743" wrote in message ... Tomkat743" wrote: =SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3)) I don't understand why the A3 won't change to A4 when I drag this formula down I need to populate 100 cells in a column and don't want to have to change it manually. As you can see I have tried both the $A3 and just A3 but nothing changes. Please help me understand. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I just don't get it please help relative reference question
No, I am telling you it should and it does, even in 2007. I had a look at
the options in 2007 and I couldn't see anything obvious that could cause such happenings. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tomkat743" wrote in message ... I BELIEVE IT SHOULD BUT IT DOESN'T!!!! I can't figure it out could it be a setting in the new 2007 office? I've changed everything I can think of but thank you for the feedback. "Bob Phillips" wrote: Bothe of those should change when you drag them down as both have a relative row number. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tomkat743" wrote in message ... Tomkat743" wrote: =SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3)) I don't understand why the A3 won't change to A4 when I drag this formula down I need to populate 100 cells in a column and don't want to have to change it manually. As you can see I have tried both the $A3 and just A3 but nothing changes. Please help me understand. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I just don't get it please help relative reference question
How do you drag down? Have you tried copy and paste? You may also want to try
this: select your 100 cells then with the top cell active click on F2 then hold down the CTRL key and hit ENTER. HTH Jean-Guy "Tomkat743" wrote: Tomkat743" wrote: =SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3)) I don't understand why the A3 won't change to A4 when I drag this formula down I need to populate 100 cells in a column and don't want to have to change it manually. As you can see I have tried both the $A3 and just A3 but nothing changes. Please help me understand. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I just don't get it please help relative reference question
You have a followup at your first post.
Tomkat743 wrote: Tomkat743" wrote: =SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3)) I don't understand why the A3 won't change to A4 when I drag this formula down I need to populate 100 cells in a column and don't want to have to change it manually. As you can see I have tried both the $A3 and just A3 but nothing changes. Please help me understand. Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
I just don't get it please help relative reference question
TomKat,
You've multiposted what's essentially this same question in the same newsgroup. Best not to multipost, as folks can wind up working on a question for you that may have been answered in the other post. Same for multiposting in different newsgroups. Just thought it worth mentioning 1) Does the formula in the first cell yield the correct result? If not, fix that first. 2) Or do you see the formula in the cell instead of the result it should yield? 3) If you put another formula, perhaps a simple one like =A3 + 1, in that same cell and drag the Fill Handle down to copy it, does it increment the cell references as it should? Does the formula work properly with something in A3? Maybe the answers to these might shed some light, and someone might figure it out. -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Tomkat743" wrote in message ... Tomkat743" wrote: =SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$I2:$U20=$A3)) - SUMPRODUCT(--('C:\Documents and Settings\Tom\Desktop\TUCSON 07\[1DLSUNDAY.xlsm]601'!$W2:$AA20=A3)) I don't understand why the A3 won't change to A4 when I drag this formula down I need to populate 100 cells in a column and don't want to have to change it manually. As you can see I have tried both the $A3 and just A3 but nothing changes. Please help me understand. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use address from relative reference across worksheets? | Excel Worksheet Functions | |||
Trying to use the INDIRECT funtion with a relative Row reference | Excel Discussion (Misc queries) | |||
Cond. Format & Absolute Cell Reference Question | Excel Discussion (Misc queries) | |||
How do I change a cell from absolute reference to relative referen | Excel Discussion (Misc queries) | |||
cell reference question | Excel Discussion (Misc queries) |