in excel, how display text in a cell while hiding a formula referencing another cell and tab?

Question

I have a spreadsheet, tab 1, whereby cell A1 displays as "account name". if i am positioned in cell A1, and i look at the fx bar, i do not see "account name", like one would think. instead, i see the following formula =detail!a1 this cell is referring to cell A1 in the adjacent tab, tab 2, of the workbook. how did the person who programmmed this do it please?. i need to know because i will need to do this sort of thing from now on, but the person who did it isn't available. thanks!

Answer 1

first, let's take apart the string itself (=detail!a1).

=
» we always start excel formulas/functions with this

detail
» is the name of another sheet in the file

!
» seperates the name of the sheet from the next part

a1
» is the target cell containing the information

as you see, all it's saying is 'display whatever is on the sheet named detail, in cell a1 of that sheet, into this cell'.

the *quick* way of creating your own linked entries like this rather than manually typing the whole reference is to:
» click the cell you want the data to appear in
» press = once
» click to whatever sheet this data is on
» click whatever cell this data is in
» press 'enter'

you will now have your own string similar to the example (=detail!a1) which will return into the cell you placed the function, whatever contents are in the target cell.

on a side note, if you try it for cells on the *same* sheet you'll see it does not put in the sheet name - you'd just see =a1 or =whatever_cell.

Answer 2

It sounds like there is already a formula in the cell that displays these ERROR types. The reason they occur is that the formula cannot act on the data in the cell that the formula is working on. In other words, your can't divide text and come up with a number so it gives an ERROR. I think you will have to fix the formula so as not to do that. You should also look at the function called ERROR, TYPE. You might be able to usit along with IF.