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.
Bookmarks