Relative Referencing not Working...Please Help

[samsilverman]

I have this LONG formula in a cell (J895). When I copy and then paste it to J937 I would expect it to act RELATIVE in respect to the rows on the same sheet. But instead, it pastes the same formula, like an ABSOLUTE.

Please help me find what I am missing. Many Thanks.

=IF(ISBLANK(L894),"",IF(OR($C890="AIRBORNE ISOLATION"),'FORMULAS & LISTS'!F$6,IF($C890="AUTOPSY ROOM",'FORMULAS & LISTS'!F$7,IF($C890="BRONCHOSCOPY",'FORMULAS & LISTS'!F$8,IF($C890="CLEAN HOLDING",'FORMULAS & LISTS'!F$9,IF($C890="CLEAN WORKROOM",'FORMULAS & LISTS'!F$10,IF($C890="CRITICAL CARE",'FORMULAS & LISTS'!F$11,IF($C890="DARKROOM",'FORMULAS & LISTS'!F$12,IF($C890="DECONTAMINATION",'FORMULAS & LISTS'!F$13,IF($C890="DELIVERY ROOM",'FORMULAS & LISTS'!F$14,IF($C890="ENDOSCOPY",'FORMULAS & LISTS'!F$15,IF($C890="ENDOSCOPY - NEGATIVE",'FORMULAS & LISTS'!F$16,IF($C890="ENDO INSTRUMENT PROCESSING",'FORMULAS & LISTS'!F$17,IF($C890="ER WAITING ROOM",'FORMULAS & LISTS'!F$18,IF($C890="ETO-STERILIZER",'FORMULAS & LISTS'!F$19,IF($C890="EXAMINATION ROOM",'FORMULAS & LISTS'!F$20,IF($C890="INTENSIVE CARE",'FORMULAS & LISTS'!F$21,IF($C890="INTERMEDIATE CARE",'FORMULAS & LISTS'!F$22,IF($C890="ISOLATION ANTEROOM - POS",'FORMULAS & LISTS'!F$23,IF($C890="ISOLATION ANTEROOM - NEG",'FORMULAS & LISTS'!F$24,IF($C890="L&D RECOVERY",'FORMULAS & LISTS'!F$25,IF($C890="LAB - BIOCHEMISTRY",'FORMULAS & LISTS'!F$26,IF($C890="LAB - CYTOLOGY",'FORMULAS & LISTS'!F$27,IF($C890="LAB - GENERAL",'FORMULAS & LISTS'!F$28,IF($C890="LAB - HISTOLOGY",'FORMULAS & LISTS'!F$29,IF($C890="LAB - MICROBIOLOGY",'FORMULAS & LISTS'!F$30,IF($C890="LAB - NUCLEAR MEDICINE",'FORMULAS & LISTS'!F$31,IF($C890="LAB - PATHOLOGY",'FORMULAS & LISTS'!F$32,IF($C890="LAB - SEROLOGY",'FORMULAS & LISTS'!F$33,IF($C890="NEWBORN INTENSIVE CARE",'FORMULAS & LISTS'!F$34,IF($C890="NONREFRIG BODY ROOM",'FORMULAS & LISTS'!F$35,IF($C890="O.R.",'FORMULAS & LISTS'!F$36,IF($C890="PATIENT ROOM",'FORMULAS & LISTS'!F$37,IF($C890="PHARMACY",'FORMULAS & LISTS'!F$38,IF($C890="PROCEDURE ROOM",'FORMULAS & LISTS'!F$39,IF($C890="PROTECTIVE ENVIRONMENT",'FORMULAS & LISTS'!F$40,IF($C890="RECOVERY",'FORMULAS & LISTS'!F$41,IF($C890="SOILED HOLDING",'FORMULAS & LISTS'!F$42,IF($C890="SOILED WORKROOM",'FORMULAS & LISTS'!F$43,IF($C890="STERILE STORAGE",'FORMULAS & LISTS'!F$44,IF($C890="STERILIZER EQUIPMENT ROOM",'FORMULAS & LISTS'!F$45,IF(AND($C890="SUBSTERILE",$L894="N/A",$L895>0),'FORMULAS & LISTS'!F$46*1,IF(AND($C890="SUBSTERILE",$L894>0,$L895>0),'FORMULAS & LISTS'!F$46*1,IF(AND($C890="SUBSTERILE",$L894="N/A",$L895<0),('FORMULAS & LISTS'!F$46*-1),IF(AND($C890="SUBSTERILE",$L894<0,$L895<0),('FORMULAS & LISTS'!F$46*-1),IF($C890="SURGICAL CYSTOSCOPIC",'FORMULAS & LISTS'!F$47,IF($C890="TOILET ROOM",'FORMULAS & LISTS'!F$48,IF($C890="TRAUMA",'FORMULAS & LISTS'!F$49,IF($C890="TREATMENT ROOM",'FORMULAS & LISTS'!F$50,IF($C890="TRIAGE",'FORMULAS & LISTS'!F$51,IF($C890="X-RAY - CATHERIZATION",'FORMULAS & LISTS'!F$52,IF($C890="X-RAY - CRITICAL CARE",'FORMULAS & LISTS'!F$53,IF($C890="X-RAY - DIAGNOSTIC",'FORMULAS & LISTS'!F$54,IF($C890="X-RAY - SURGICAL",'FORMULAS & LISTS'!F$55,IF($C890="X-RAY - TREATMENT",'FORMULAS & LISTS'!F$56)))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[SteveO59L]

You're not seriously expecting someone to unscramble that are you ?

 

[Peter_SSs]

There *HAS* to be a better way to write that formula (but I'm not going to try to work out what it is). However, I can report that when I do copy (Excel 2007) that formula to another row, the row references *do* update as relative references.

 

[samsilverman]

Thanks guys and I apologize for the post. It was late and I was quite perplexed and frustrated (being the newbie and all).

I knew in my head that it should copy and paste fine, but it wasn't doing it. This morning I tried it again after having closed and reopening the file and it works fine. Perhaps it has something to do with having a lot of formulas and a few macros in the workbook. Or, maybe it's Excel's way of laughing at me.

Anyhow, thanks for at least looking in on it.

 

[Norie]

Have you considered ditching the formula?

Like the other guys I'm not going to spend time trying to fully decipher but from what I have looked at it seems you could use a simple VLOOKUP.

 

[samsilverman]

Thanks for the suggestion, Norie. I don't know what VLOOKUP is, but I'll look into it.

 

[Norie]

VLOOKUP is one of the most used functions in Excel.

If you had your data in some sort of table/list format.

You would have the values you want to find in the first column and what you want to return in the column to the right of that.

Looking at your formula it seems you might already have a setup like that or something similar.

 

[dapo]

Looks like a good case for dragging and letting Excel figure it out. But I have to say I'm impressed with that FORMULA! of your. What are you doing? Figuring out how to get to the moon? Glad you got it figured out.

 

[Jonmo1]

Yep, vlookup should work wonders for that formula....

But I think the problem with your references not updating relatively...
It's probalby something to do with HOW you copy pasted the formula...

I would guess you probably double clicked on the cell you pasted to (accidentally or intentionally - doesn't matter).
By doing that, you went into EDIT mode, and the formula got pasted exactly in the cell as a text string.
As opposed to doing a single click on the destination cell and then pasting. This way, the references update relatively.

 

[Norie]

Untested:

=VLOOKUP($C890, 'FORMULAS & LISTS'!$F6$G56, 2, 0)

This would assume you have the values to look for in column F and the values to return in column G.