Formula Coding Turning Up Blank

[andyfronek]

Hello, I am trying to reference a cell on the first sheet in the workbook I'm editing. I named the cell I want to reference "qpv" standing for quotation product value, this cell is on the first sheet. On a later sheet in the workbook, there is a cell with the code =IF(qpv>0,qpv,0) and the cell shows up blank (but should have the value of 15500). I tried the same exact coding in the cell a few rows away (cell C14) and the code works. I then entered a random number (8) into the cell to see if it came up blank, and the result was "$8". I then tried, because the code worked in the cell a few rows away, to directly reference the cell the code had worked in. The code I used was =C14. Nothing showed up. I went to the data>validation menu and it was set on "any value" so I'm not quite sure what's going on. Any help would be splendid! Thanks!

[Ron Coderre]

What's the exact range reference (from the Insert.Name.Define window) for qpv?

[shg]

How did you name the cell? It sounds like you ended up with a relative reference.

[andyfronek]

I believe that the cell reference 'Quotation Summary'!K201 and I renamed that cell "qpv"... I'm not exactly sure what you mean by relative reference. I'll go look it up! I named the cell by clicking in the name box to the left of the function line (which had K201) in it and typed "qpv" and pressed enter. This method of reference worked for a random cell I tested it on after it didn't work in the cell I wanted. I didn't make the sheet, I'm just editing it. I was wondering if there was perhaps some sort of protection or validation I'm not aware of...or if I'm just referencing it wrong, but I don't think I am. Thanks for the responses!

[Ron Coderre]

Try changing the reference to this:

Please Login or Register  to view this content.

The dollar signs ($) "lock" that part of the reference.

Does that resolve the issue?

[andyfronek]

No it doesn't... I'm very confused right now because it used to show up in a random adjacent cell but now no matter which cell I put your reference code in the cells show up blank. Any ideas? I appreciate your help!

[shg]

Post the workbook?

[andyfronek]

Sorry it's too big even if I delete all the sheets unnessary to my problem. I'm not sure if there is a better way to make it smaller, if there is let me know because I really want to get this solved! I took screen shots and attached those instead! The .jpg named "1" is the summary sheet with the cell I want referenced with an arrow pointing to it. "2" is the sheet I want to reference the cell to... You'll see it! Thanks

[shg]

That's not much help.

You can delete everything in the workbook except the sheet where the name is defined, and you can delete the cell contents of that.