# give number value in one cell based on a number being generated from another formula on a separate sheet

Trying to get a specific number to generate when another cell gives a number from a different sheet.

If the below formula gives back the number, different cell on different sheet gets a value.

``````=IF(COUNTIF(B:B,A4)=0,"Missing",A4)
``````

Kinda confused.

• You post is also kind of confusing.... Can you post sample data, and clearly state your expected result, and how it differs from what you get. – cybernetic.nomad Jul 26 '18 at 19:40
• It isn't clear what's confusing you. You show a formula in your question whose result depends on the result in another cell. It doesn't make any difference how that other cell got it's result -- formula, stored entry, etc. A result in one cell doesn't drive something into another cell. The other cell just references the source cell, like you did here. If the cell reference is on the same sheet, all you need is the cell reference. If it is on a different sheet, you prefix the cell reference with the sheet name. Can you clarify your question? – fixer1234 Jul 26 '18 at 22:18
• Seems you are trying to match `A4` with `Col B` in different sheet and if returns 0 then "Missing" as `True` otherwise `A4` as `False`. In that case Data range must preceded by Sheet name. Like this, `=IF(COUNTIF(Sheet2!B117:B121,A4)>0,"Missing",A4)` or this may also work, `=IF(VLOOKUP(A4,Sheet2!B117:B120,1,FALSE)=0,"Missing",A4)` – Rajesh S Jul 27 '18 at 8:50

## 1 Answer

to refer to a cell in another sheet, let's say for instance cell B5 from sheet "MySheet", you can use this syntax :

``````MySheet!B5
``````

(instead of using simply `B5` to refer to the cell B5 in the current sheet)

so to answer your precise question, if you have your formula in the cell B5 of sheet "FirstSheet", and you want to refer it in another cell of another sheet, you could do that :

`````` =IF(NOT(EXACT(MySheet!B5, "Missing")), "The value you want")
``````

(The cell with this formula will be "The value you want" if and only if the cell B5 in MySheet does not contain the string "Missing")