+ Reply to Thread
Results 1 to 4 of 4

Copy cells containing $ sign but still adjust cell number?

  1. #1
    Registered User
    Join Date
    07-13-2007
    Posts
    2

    Copy cells containing $ sign but still adjust cell number?

    Hi all,

    I hope I can convey what I am trying to do here and what my problem is.

    If I have a formula in cell AE3 which is
    =COUNTA(B3,D3,F3,H3,J3,L3,P3,R3,N3,T3,V3,X3,AA3) and I copy it to the cell below it then obviously becomes
    =COUNTA(B4,D4,F4,H4,J4,L4,P4,R4,N4,T4,V4,X4,AA4).

    For reasons too long to explain I need to actually have the contents of the cell as =COUNTA($B$3,$D$3,$F$3,$H$3,$J$3,$L$3,$P$3,$R$3,$N$3,$T$3, $V$3,$X$3,$AA$3) and the one below as =COUNTA($B$4,$D$4,$F$4,$H$4,$J$4,$L$4,$P$4,$R$4,$N$4,$T$4, $V$4,$X$4,$AA$4).

    The problem with this is that when I copy the original cell if it contains $ signs then of course it does what I ask and the cell references stay exactly the same when infact I DO want them to change as if there was no $ sign there.


    Is it possible to achieve what I want without copying having to copy the cells with no dollar sign to start with all the way down (500 cells!) so that the cell references are correct and then manually edit the contents of each cell and add the dollar sign using the F4 key?



    EDIT: Oops! I've just discovered that the use of the $ sign doesn't actually achieve what I want anyway.
    The problem I am having is that when someone moves a cell which forms part of the count the forumla is being too clever for it's own good and updating to the cell address of where I have moved the cell to.
    Is there a way to prevent this happening? I thought that the $ was going to do this for me but now I am stuck.

    Edit 2: It appears that the solution I require is using the offset function. I can return the count of a single cell but now need to return the count of selected offset cells. Unfortunately I can not use a range here as I am counting the countents of alternate columns. Anyone got any pointers for this please?

    Edit 3: I have managed to solve my problem.
    To achieve what I wanted to which I thought $ would do I have ended up with the formula :-
    =COUNTA(OFFSET(AE3,0,-4,1,1),OFFSET(AE3,0,-7,1,1),OFFSET(AE3,0,-9,1,1),OFFSET(AE3,0,-11,1,1),OFFSET(AE3,0,-13,1,1),OFFSET(AE3,0,-15,1,1),OFFSET(AE3,0,-17,1,1),OFFSET(AE3,0,-19,1,1),OFFSET(AE3,0,-21,1,1),OFFSET(AE3,0,-23,1,1),OFFSET(AE3,0,-25,1,1),OFFSET(AE3,0,-27,1,1),OFFSET(AE3,0,-29,1,1))

    Hopefully this will help anyone in the future if they do a topic search and find this thread.
    Last edited by oatfedgoat; 07-13-2007 at 06:44 AM.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by oatfedgoat
    Hi all,

    I hope I can convey what I am trying to do here and what my problem is.

    If I have a formula in cell AE3 which is
    =COUNTA(B3,D3,F3,H3,J3,L3,P3,R3,N3,T3,V3,X3,AA3) and I copy it to the cell below it then obviously becomes
    =COUNTA(B4,D4,F4,H4,J4,L4,P4,R4,N4,T4,V4,X4,AA4).

    For reasons too long to explain I need to actually have the contents of the cell as =COUNTA($B$3,$D$3,$F$3,$H$3,$J$3,$L$3,$P$3,$R$3,$N$3,$T$3, $V$3,$X$3,$AA$3) and the one below as =COUNTA($B$4,$D$4,$F$4,$H$4,$J$4,$L$4,$P$4,$R$4,$N$4,$T$4, $V$4,$X$4,$AA$4).

    The problem with this is that when I copy the original cell if it contains $ signs then of course it does what I ask and the cell references stay exactly the same when infact I DO want them to change as if there was no $ sign there.


    Is it possible to achieve what I want without copying having to copy the cells with no dollar sign to start with all the way down (500 cells!) so that the cell references are correct and then manually edit the contents of each cell and add the dollar sign using the F4 key?



    EDIT: Oops! I've just discovered that the use of the $ sign doesn't actually achieve what I want anyway.
    The problem I am having is that when someone moves a cell which forms part of the count the forumla is being too clever for it's own good and updating to the cell address of where I have moved the cell to.
    Is there a way to prevent this happening? I thought that the $ was going to do this for me but now I am stuck.

    Edit 2: It appears that the solution I require is using the offset function. I can return the count of a single cell but now need to return the count of selected offset cells. Unfortunately I can not use a range here as I am counting the countents of alternate columns. Anyone got any pointers for this please?

    Edit 3: I have managed to solve my problem.
    To achieve what I wanted to which I thought $ would do I have ended up with the formula :-
    =COUNTA(OFFSET(AE3,0,-4,1,1),OFFSET(AE3,0,-7,1,1),OFFSET(AE3,0,-9,1,1),OFFSET(AE3,0,-11,1,1),OFFSET(AE3,0,-13,1,1),OFFSET(AE3,0,-15,1,1),OFFSET(AE3,0,-17,1,1),OFFSET(AE3,0,-19,1,1),OFFSET(AE3,0,-21,1,1),OFFSET(AE3,0,-23,1,1),OFFSET(AE3,0,-25,1,1),OFFSET(AE3,0,-27,1,1),OFFSET(AE3,0,-29,1,1))

    Hopefully this will help anyone in the future if they do a topic search and find this thread.
    Hi,

    you appear to have mis-understood the $ function (as did we all, once)

    The $ fixes the formula to a row ($1) or a column ($A) or a cell ($A$1)

    The $ does NOT fix the formula to the first cell in the leftmost column.

    If you move the indicated row, column or cell the formula will follow the cell to which it was assiged.

    If you delete the column or row the formula will error.

    To fix the formula to the first cell you could use =Offset(A1,0,0) or the =Indirect("A"&1)

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    07-13-2007
    Posts
    2
    Quote Originally Posted by Bryan Hessey


    To fix the formula to the first cell you could use =Offset(A1,0,0) or the =Indirect("A"&1)

    hth
    ---
    HI thanks for the reply. You are correct, I soon found the true way in which the $ parameter works.
    I had in my mind that I wanted all cells related to a particular one, when infact as you suggest I could simply use the offset command and point it at the actual cell I wanted instead of counting how many to the left it was etc.

    Ooopsy!

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by oatfedgoat
    HI thanks for the reply. You are correct, I soon found the true way in which the $ parameter works.
    I had in my mind that I wanted all cells related to a particular one, when infact as you suggest I could simply use the offset command and point it at the actual cell I wanted instead of counting how many to the left it was etc.

    Ooopsy!
    Hi,

    Understanding the $ is a bit of a trap, but easy to avoid. I guess I tend now to think more in 'cells' and in 'places where cells could be' ie location addresses.

    Good to see you have resolved your problem, and thanks for your feedback.

    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1