Retrieve value from a range of cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EMoe - 19 Jun 2006 17:00 GMT

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

Signature

EMoe

Reply to this Message

VBA Noob - 19 Jun 2006 17:06 GMT

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

Signature

VBA Noob

Reply to this Message

SteveG - 19 Jun 2006 17:40 GMT

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

Signature

SteveG

Reply to this Message

EMoe - 19 Jun 2006 19:02 GMT

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

Signature

EMoe

Reply to this Message