A1 Reference Styles

What is the difference between saying
B2, $B2, B$2
When do we use the first one vs the rest?

I have a spreadsheet that uses all the three styles all over - I can't make the difference.

And I noticed that when I open this spreadsheet in a C# program, I'm unable to get values for the cells that have a '$' in them.

It anchors the row/column reference when it is copied over a range.

If you type in B1, =A1 and copy it down the =A1 will become =A2 and so on.

If you type in B1, =A$1 and copy it down it will remain =A$1

Similarly if you copy across columns you can keep the column reference fixed.

By using =$A$1 you keep both fixed.

I know nothing about C# I'm afraid.

Thanks!
I guess this helps for me to investigate more..

Of course if your formulae are all entered perhaps you could use Find and Replace to get rid of your $s before opening in C#.

The $ symbol before a column letter or row number makes that reference absolute rather than relative to the position of the cell containing the reference. That is, if cell C2 has a reference to B2 (e.g. "=B2+1") and you copy the formula in C2 to C3, then that reference will change (to "=B3+1"). If you copied the formula in C2 to D4, you would get "=c4+1". So in this case "B2" really means "the cell immediately to the left of this one". If the formula in C2 was "=$B2+1" then copying it to D4 would change it to "=$B4+1" i.e. the column stays the same but the row changes. So "$B2" means, in this case, "the cell in column B, in the same row as the current cell". "B$2" would mean the column changes relative to the referring cell and the row stays the same, and "$B$2" always refers to cell B2. Inserting or deleting rows or columns *will* adjust these absolute references.

Often these references are used when you have a constant value (e.g. column heading, tax rate) that you want to use in a formula with some data.

Great! Thanks a lot!

Your answers helped me to eliminate the possibility of having excel exceptions(inside C#). Looks like I'm having some COM exceptions for a different reason - I'll have to narrow it down..