Calling Excel Guru's

[Rowee]

So my wife often asks me Excel questions as the 'Excel guy' at her work that people insist on getting to do things is, well, just not as good as you'd think he should be.
She works at a bank so unfortunately I cannot share the actual spreadsheets for obvious reasons, but the issue is easy to explain.

There is a list of employee details with a couple of columns of numbers that relate to each employee. Trying to see the difference between 2 sets of numbers, she essentially subtracted one number from the other, but in doing so she gets a response from Excel I've not seen before.

She starts with '=' followed by clicking on the first cell of the equation (which would then be followed by '-' then clicking on the second cell)

However, as soon as she clicks the first cell, instead of inserting the cell reference like you would expect, it inserts the word Note. This only happens for one column. So when she completes the sequence above, the formula reads =Note-G6 and the response is #NAME?

Naturally I just manually typed in the cell reference so she could continue working, but I'm intrigued by what this guy has done to generate the Note reference. Has anyone seen it before? There is no note/comment on the cell she clicks, it is a simple cell, formatted to general that contains a number, not a formula. And I've just checked, you can actually remove the data from the cell so it is empty, type = and click the cell, you end up with =Note

Any ideas? It's bugging the hell out of me

 

[Alastair]

It sounds like a named range has been created, either as a specific named range or as part of a table. In this case it sounds like it may be a table.

There's a way to see all Names in a workbook, I think it's Formulas>Defined Names

 

[DorsetDude]

Long shot but maybe there's hidden columns or rows in the sheet causing the issue.

 

[Matt-P]

Think Alastair has it, sounds like a named range to me.

Formula -> Name Manager will show all of them

or

Click on the cell in question and see if the Name Box (LHS of screen on a level with formula bar what usually shows the cell reference A10 etc) shows the word Note when you click on it instead of the cell reference.

 

[Alastair]

I'm thinking it's maybe a borked attempt at a table (or a lazily re-used spreadsheet with the remains of a dead table littering the worksheet), one column is within the table and is a named range "Note" and the other column is outside the table. Entering the formula Excel is getting confused as of this type of calculation was entirely within a table entering it once in the top data row would auto-populate the entire table. The reason that manually entering the formula works is that bypasses Excel's attempts to be helpful and predict what you want to do (it's trying to do the Table-thing).

And it's really good practice with Excel to avoid using General formatting, use either Number or Currency (whichever is the most appropriate). It's a very low level data validation check and it can help avoid problems down the line. I occasionally develop Excel spreadsheets for handling really big datasets (250k+ data rows) and anything that stops someone entering the wrong type of data into a cell saves time trouble-shooting later!

 

[Rowee]

It sounds like a named range has been created, either as a specific named range or as part of a table. In this case it sounds like it may be a table.

There's a way to see all Names in a workbook, I think it's Formulas>Defined Names

Long shot but maybe there's hidden columns or rows in the sheet causing the issue.

Think Alastair has it, sounds like a named range to me.

Formula -> Name Manager will show all of them

or

Click on the cell in question and see if the Name Box (LHS of screen on a level with formula bar what usually shows the cell reference A10 etc) shows the word Note when you click on it instead of the cell reference.

I'm thinking it's maybe a borked attempt at a table (or a lazily re-used spreadsheet with the remains of a dead table littering the worksheet), one column is within the table and is a named range "Note" and the other column is outside the table. Entering the formula Excel is getting confused as of this type of calculation was entirely within a table entering it once in the top data row would auto-populate the entire table. The reason that manually entering the formula works is that bypasses Excel's attempts to be helpful and predict what you want to do (it's trying to do the Table-thing).

And it's really good practice with Excel to avoid using General formatting, use either Number or Currency (whichever is the most appropriate). It's a very low level data validation check and it can help avoid problems down the line. I occasionally develop Excel spreadsheets for handling really big datasets (250k+ data rows) and anything that stops someone entering the wrong type of data into a cell saves time trouble-shooting later!

It was indeed an old named range. I should have known really as there are multiple named ranges in the sheet, I just hadn't expect Excel to react like that mid-formula. The named range was just that single cell, I guess in the past it was used as a note, all very odd.
I agree Alastair, using the General format isn't ideal for most instances, but unfortunately I need to be a little careful with what I do on their spreadsheets. I'd imagine, technically, I'm not allowed to see the bank data so end up biting my tongue about most things in the spreadsheet's structure.

Thanks all for your help!