Retrieve value from a range of cells

[EMoe]

Hello Experts!

I usually post for help in the programming section.

I'm looking for a formula that looks at a range of cells (say A1:A20) and retrieves the value within that range.

In those ranges, each cell has a formula that will return a value if the criteria is true. In those ranges there will only be one number, because only one can be true at a time. I just need to get that value in another cell.

I tried to do =sum(A1:A20), but the formula doesn't recognize the values as numbers (I guess).

Any help, much appreciated,
EMoe

[VBA Noob]

Do you have some samples of formulas and what the return value might be. Will all the formulas have a value

[SteveG]

EMoe,

If your range A1:A20 contains values stored as text you could try this to retrieve the data and convert to a number.

=IF(ISNA(VLOOKUP("*",A1:A20,1,)*1),"",VLOOKUP("*",A1:A20,1,)*1)

If there are no values, VLOOKUP returns the N/A error so by adding the IF ISNA to the formula, it will return a blank cell instead. Multiplying by 1 converts to a number.

HTH

Steve

[EMoe]

Thanks Noob & Steve.

What I had was, in cell B2, I was extracting a process value, using a formula, from another application, using an addin.

The value in that cell, for example would be a 1. Which is a logic true value, simply stating that a silo is selected (1=Selected, 0=Not Selected).

I had a formula in the ajacent cell C2 =If(B2,=1,"1","") which means that if the cell has a 1, then be 18 (which would be silo 18).

Well from B2:B20 are silo's 1-18. Only one silo can be selected at a time. So what I was looking for was a formula that would look down C2:C20 and pick out the only value that was there. The other cells would be a 0.

I couldn't get your formula to work Steve.
What I ended up with was changing the above formula from what you see to =If(B2=1,1,0). At the bottom of the range I have =sum(C2:C20), and I get the value that I need.

Sorry I wasn't clear in the beginning. I'll be glad to look at any other solution that you may have.

Thanks a bunch again,
EMoe