excel #ref issue

[mark-gabb]

hi everyone

having an issue with a complicated excel sheet to display patient status and discharge status at the hospital i work at....

i have a series of formulas that add up length of stay and days till discharge, as well as copying a series of cells onto a second sheet to create a print out

when patient are moved from one bed to another, im finding that the formulas either follow the patient and thus show calculations not for the correct patient in the same row, or give me a #ref error




Ive tried putting in $ signs to stop them from moving but they seem to move regardless...

ive used a macro to repair upon command when this happens, but im also using it as a shared workbook and the users are not able to turn sharing on or off....well they can do it, but i would really really prefer them not to...

is there a setting in excel i can use to stop the formulas from jumping around when ever they want?

[Catalin.B]

you have to upload a sample to clarify what is happening...
How do you "move" your pacients ? I mean in your sheet..

[mark-gabb]

https://skydrive.live.com/redir.aspx...F14BAD69FF!104

when you move say the first patient down two or three rows sometimes the formulas will follow, or erase there reference to other cells, maybe there is a better way to run the formulas that im not aware of, ive mostly just fiddled untill i got it right.....hopefully someone here knows how to do this better than i do....

biggest problem is that it must work as a shared workbook.


at the moment each cell must be cut and pasted individually due to the issue of protected cells stopping the highlight of a whole row at once

[Pecoflyer]

On your sheet "Current Hand Over" you use absolute references ( fi $D$8). When moved to another row, this ref will not change

To make sure, the ref changes when you change the row, use $D8 instead.


PS I hope the data you have published is a dummy....

[mark-gabb]

heh heh\


yes the data is most definatly dummy data, something people at work have been whipping up for a while for just these kind of tests....

you were correct, using the absolute references it has stopped the formulas altering themselves as data is cut and pasted, but am still finding every now and then when i go down to check a cell will have gone to something like

=if(#ref-#ref) it seems to forget the cells it is meant to link to

[Pecoflyer]

It would be nice if you could post a sample sheet showing the error

[mark-gabb]

ok have attached document psag errors
on main sheet i moved bill brigs from room 6 bed 4 to room 5 bed 1
and jack johnson from room 6 bed 2 to room 5 bed 4

on the hand over sheet bill briggs and jack johnson are both still in the same beds, and ive got #ref on the beds they are meant to be in

even with dollar signs this happens the fomula on the hand over sheet that states where the cell gets its information from follow the cut and pasted name
and the location you paste to give you a #ref error code

[Pecoflyer]

In your example sheet you have shown the " after" but not the " before" at the same time.

In this sheet, cell references I could see are totally relative instead of partially.

How do you transfer the data? Copy Paste ? Dragging ?

I'm also a bit concerned about the merged cells in col A, they usually mean trouble