Show Value When Range is Not Empty

[Regouin]

Ok, probably a fairly simple problem, but i cant get it right.

I want a certain cell (a1) to show a certain value from a different sheet (sheet2!a1) but only show this value when any of the cells in the following range (sheet2!h2:h45) are not empty. Most logical is =IF(sheet2!h2:h45="";"";sheet2!a1) but this only works when the majority of the cells in the range show a value, now i want it to also display sheet2!a1 when only 1 cell in the range shows a value. preferrably no VBA and keep the formula as short as possible.

TIA
frank

[Jacob Hilderbrand]

Try this:


=IF(COUNTA(Sheet2!H2:H45)=1,Sheet2!A1,"")

[Regouin]

once again you got the job done, only had to make a minor adjustment because it should also show when there are more then 1 value present.

=IF(COUNTA(Sheet2!H2:H45)>=1,Sheet2!A1,"")
now it works fine.

[Jacob Hilderbrand]

I thought you wanted it for when only 1 cell had a value?

Anyways, glad you got it working. With CountA you can determine how many cells have values or not.

:beerchug:

[Regouin]

ok, the problem continues, now i am putting IF equations in these cells H2:H45, the CountA equation considers this a value, but by default the output of these if equations is blank(""), so i want the
=IF(COUNTA(Sheet2!H2:H45)>=1,Sheet2!A1,"") equation to only return a value when one or more of the cells in the given range is not blank, but they do contain formulas.

ok got it, with a countif function instead of counta