Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-07-2011, 09:12 PM
mark-gabb mark-gabb is offline excel #ref issue Windows XP excel #ref issue Office 2007
Novice
excel #ref issue
 
Join Date: Nov 2011
Posts: 17
mark-gabb is on a distinguished road
Unhappy excel #ref issue

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?
Reply With Quote
  #2  
Old 11-08-2011, 02:15 AM
Catalin.B Catalin.B is offline excel #ref issue Windows Vista excel #ref issue Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

you have to upload a sample to clarify what is happening...
How do you "move" your pacients ? I mean in your sheet..
Reply With Quote
  #3  
Old 11-09-2011, 03:05 PM
mark-gabb mark-gabb is offline excel #ref issue Windows XP excel #ref issue Office 2007
Novice
excel #ref issue
 
Join Date: Nov 2011
Posts: 17
mark-gabb is on a distinguished road
Default

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

Last edited by mark-gabb; 11-09-2011 at 07:13 PM. Reason: left something out
Reply With Quote
  #4  
Old 11-15-2011, 12:41 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline excel #ref issue Windows XP excel #ref issue Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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....
Reply With Quote
  #5  
Old 11-15-2011, 02:26 PM
mark-gabb mark-gabb is offline excel #ref issue Windows XP excel #ref issue Office 2007
Novice
excel #ref issue
 
Join Date: Nov 2011
Posts: 17
mark-gabb is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 11-16-2011, 02:45 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline excel #ref issue Windows XP excel #ref issue Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

It would be nice if you could post a sample sheet showing the error
Reply With Quote
  #7  
Old 11-16-2011, 03:11 PM
mark-gabb mark-gabb is offline excel #ref issue Windows XP excel #ref issue Office 2007
Novice
excel #ref issue
 
Join Date: Nov 2011
Posts: 17
mark-gabb is on a distinguished road
Default

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
Attached Files
File Type: xlsx PSAG errors.xlsx (60.9 KB, 7 views)
Reply With Quote
  #8  
Old 11-20-2011, 02:32 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline excel #ref issue Windows XP excel #ref issue Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel #ref issue Not Sure If WORD Or Excel Issue? cbjroms Excel 3 10-22-2011 10:31 PM
Printing Issue with Excel 2010 Amyrlin Excel 4 05-16-2011 09:56 AM
excel #ref issue excel print issue excel issue Excel 7 05-10-2011 12:32 AM
excel #ref issue Excel data using mailmerge issue kilosub Mail Merge 12 05-05-2011 12:27 AM
Excel 07-Access 03 Data Link Issue ddw23 Excel 0 08-20-2010 09:05 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft