Combine contents of several cells into one

[Bedford]

Trying to combine the contents of several cells in sheet 1 for example, which each have simple formulas, example; =IF(C46="","",C46-28), and have the results of these cells in sheet 1 appear in a single cell in sheet 2. The cells in sheet 1 that I wish to combine in sheet 2, will only see one of these several cells having a result based on input from, example; cell C46 in the formula, the other cells with formulas will not be calculating a result as the preceding cell will be empty.
I'm not sure I'm making sense of this?
Thanks.

 

[Anthony47]

Not sure I got your question...
If you wish to get the first non-empty cell, then the simplest concept is this:

=If(Sheet1!A2<>"",Sheet1!A2,IF(Sheet1!B2<>"",Sheet1!B2,IF(Sheet1!C2<>"",Sheet1!C2,"No value")))

Of corse if you have to choose in many many cells, other approach could be simpler than the nested-if approach (the formula that I suggested), but this depends also on how the cells are arraged (contiguous /random) and the Excel Version you use.

Bye

 

[Bedford]

Hi Anthony, that formula worked quite well thank you. Much appreciated!

 

[Bedford]

I may have spoke too soon. The formula works well for return of the values from sheet 1, however in sheet 2 in a dependent cell which has the formula; =IF(B7="","",B7-24), it returns a #VALUE! error, likely because of the "No Value" in the formula. Is there a work around for that?
Thank you.

 

[Anthony47]

My formula returns "No value" when you it do not pick any of the cells; it was intended to clearly show that situation, but it is up to you to decide what to do when none of the entries are available... Is that an error situation? Leave the formula as suggested or (better) replace "No value" with NA() . Is than normal? Maybe returning 0 instead of "No value" would be better...

Bye

 

[Bedford]

Indeed the replacing of "No value" with something else returns that other value of something else. The error; #VALUE! is a result of a dependent cell, B13, which contains the formula; =IF(B7="","",B7-24), the formula you provided is in cell B7. Any thoughts on how to have cell B13 show "No value" instead of the error?

 

[Anthony47]

The logical way, in B13:

=IFERROR(IF(B7="","",B7-24),"No Value")

In other words:

=IFERROR(YourOriginalFormula,"No value")

But with this specific formula, you could use

=IF(B7="No value","No Value",B7-24)

Bye

 

[Bedford]

Brilliant!! Thanks again.