From what I have gleaned, the way to get a cell's width in Excel is to enter the formula =CELL("width") into the cell in question. This works fine for one cell at a time, but when I copy the formula into a second cell, the value displayed in the first cell shows the width of the second cell. If I paste the formula into a third cell, all three cells now show the width of the last cell. What's up with this?
2 Answers
Easiest way to find out the width of a cell is to right click on the column letter/row number and select "column width.." or "row width.."
This will pop up a window showing you the width. (All cells in the same column are going to be the same, however all the cells in a row are not going to be the same.
=CELL("WIDTH",$A1)Try the above formula (this will only work if you paste downwards. Id you want to copy the formula across the spreadsheet, try this
=CEL("WIDTH",A$1)The behavior is because the formula (with no cell address parameter) shows the width (approximate width, usually) of the currently selected cell at the moment calculation is forced.
So when you enter it in, say, A1, calculation occurs and that's the currently selected cell, so it shows the width of column A.
When you enter it in, say, B1, B1 is the currently selected cell so its column, column B, is the value the formula returns. ALL instances of the formula, regardless of their locations.
This is why you use the cell address parameter...
You can make use of this effect by selecting the address item for `CELL() to lookup:
=CELL("address")Enter it where you want to know the address of the currently selected cell and then, whenever you find yourself wondering what your currently selected cell is, press F9 to force calculation and the cell you put the formula in will show the current cell's address.
You can even wrap it with INDIRECT() so you can get the value that is in the currently selected cell.
Note that when you have the cell that information will appear in and enter the formula, Excel will usually offer a concern about circular referencing, but that would be expected, eh? When you select some other cell and force calculation, that won't happen because the selected cell is no longer the cell with the formula in it so there is no circularity.
That might be of limited use as you know the value since you "are there", but you can also make that step part of a bigger formula rather than a standalone formula. Or as a standalone formula that triggers elements of other formulas. So you could arrange for someone to select a cell with a company name, in a list/table, and this effect might allow a formula that calculates a credit terms and limits offer might act. All without entering the information in some cell. So the workbook and all its pages/data could be utterly protected and yet, someone can still have a result generated, all without any programming (VBA, etc.) or form controls.
But again, avoiding what you mention in the question requires using a cell address as a second parameter in the function. Then it's like any other function vis-a-vis what it references.
