I hope there is an easy answer to my formula problem so I will try to explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The problem I
am having is that I have 4 more ranges of cells and each range of those cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I enter a
soil type from the first range of soil types. Basically I need to add on to
the formula above.

Also if there is a way to shorten the formula and have it recognize a range
of cells...I know you can do that but I was getting an error when I tried to
highlight the entire range so I had to click each cell individually to get it
to work. Any tips or help would be greatly appreciated.

Try MATCH() function..You can have more criterias and change the range

=IF(ISNA(MATCH(C11,Q34:Q56,0)),0,R5)

If this post helps click Yes
---------------
Jacob Skaria


"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The problem I
am having is that I have 4 more ranges of cells and each range of those cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I enter a
soil type from the first range of soil types. Basically I need to add on to
the formula above.

Also if there is a way to shorten the formula and have it recognize a range
of cells...I know you can do that but I was getting an error when I tried to
highlight the entire range so I had to click each cell individually to get it
to work. Any tips or help would be greatly appreciated.

Let's start by shortening your formula:

=IF(COUNTIF(Q34:Q56,C11),R5,0)

--
Biff
Microsoft Excel MVP


"Keith - NRCS" <Keith - wrote in message
...
I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I enter
a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to get
it
to work. Any tips or help would be greatly appreciated.

If you have soil type in Q34:Q56 and corresponding dollar in R34:R56 try the
below formula which will return the formula

=IF(ISNA(MATCH(C11,Q34:Q56,0)),0,INDEX(R34:R56,MAT CH(C11,Q34:Q56,0)))

If this post helps click Yes
---------------
Jacob Skaria


"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The problem I
am having is that I have 4 more ranges of cells and each range of those cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I enter a
soil type from the first range of soil types. Basically I need to add on to
the formula above.

Also if there is a way to shorten the formula and have it recognize a range
of cells...I know you can do that but I was getting an error when I tried to
highlight the entire range so I had to click each cell individually to get it
to work. Any tips or help would be greatly appreciated.

This may better explain my data. The numbers on the right are the soil types.
They are mostly numbers but some are numbers and letters.
I have a worksheet where you can enter the soil type (numbers and letters)
and then in a column next to that it will automatically bring in
the dollar amount.

Does that help explain it better?

SRR Soil Map Unit Symbols
$119 35 247 362 525 1016 1030 1075 1080 1084 1091 1095 1834
27B 327B 39A 39B 41A 41B 611C 920C2 920D2 960F L205A

$129 255 269 392 517 539 1228 1213C 920B 921C2 960D2 L13A L200A

$141 112 114 386 1092 1833 1229B 944B 945C2 L163A L84A

$154 86 313 956 978 1213B L107A L184A L185B L34A

$168 109 113 336 414 887B 945B L83A

$181 118 239 102B 106B 1204B 1207B 1901B 238B 94B L85A


Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The problem I
am having is that I have 4 more ranges of cells and each range of those cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I enter a
soil type from the first range of soil types. Basically I need to add on to
the formula above.

Also if there is a way to shorten the formula and have it recognize a range
of cells...I know you can do that but I was getting an error when I tried to
highlight the entire range so I had to click each cell individually to get it
to work. Any tips or help would be greatly appreciated.

Hmmm...

You'd be better off if you could create *one* table instead of 6.

Like this:

Type...Price
35...119
247...119
362...119
255...129
269...129
392...129
112...141
114...141
386...141
etc
etc
etc

Then, if C11 = user entered soil type:

=SUMIF(A2:A50,C11,B2:B50)


--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
This may better explain my data. The numbers on the right are the soil
types.
They are mostly numbers but some are numbers and letters.
I have a worksheet where you can enter the soil type (numbers and letters)
and then in a column next to that it will automatically bring in
the dollar amount.

Does that help explain it better?

SRR Soil Map Unit Symbols
$119 35 247 362 525 1016 1030 1075 1080 1084 1091 1095 1834
27B 327B 39A 39B 41A 41B 611C 920C2 920D2 960F L205A

$129 255 269 392 517 539 1228 1213C 920B 921C2 960D2 L13A L200A

$141 112 114 386 1092 1833 1229B 944B 945C2 L163A L84A

$154 86 313 956 978 1213B L107A L184A L185B L34A

$168 109 113 336 414 887B 945B L83A

$181 118 239 102B 106B 1204B 1207B 1901B 238B 94B L85A


Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

Maybe I am over thinking this. Will it be easier to do if I organize my data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

....etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The problem I
am having is that I have 4 more ranges of cells and each range of those cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I enter a
soil type from the first range of soil types. Basically I need to add on to
the formula above.

Also if there is a way to shorten the formula and have it recognize a range
of cells...I know you can do that but I was getting an error when I tried to
highlight the entire range so I had to click each cell individually to get it
to work. Any tips or help would be greatly appreciated.

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

Biff, is there a change in your email id from what is mentioned in the
profile..

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

Thanks Biff!

Now one more question. When I paste the formula into the cell it works fine.
But how do I drag it down or copy it to the rest of the column without the
cell numbers ascending automatically?

"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

Use absolute referencing. Below are the different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

If this post helps click Yes
---------------
Jacob Skaria


"Keith - NRCS" wrote:

Thanks Biff!

Now one more question. When I paste the formula into the cell it works fine.
But how do I drag it down or copy it to the rest of the column without the
cell numbers ascending automatically?

"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

If you mean , that's a bogus address. Spam that
address all you want!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Biff, is there a change in your email id from what is mentioned in the
profile..

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to
their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell
C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of
those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add
on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I
tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

To you query yesterday 'about using alias name' I have send a mail which
bounced back..and hence checked..

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

If you mean , that's a bogus address. Spam that
address all you want!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Biff, is there a change in your email id from what is mentioned in the
profile..

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to
their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell
C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of
those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add
on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I
tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

OK...I ran into another snag with it. It works great for the soil types that
start with a number but it enters a "0" in the cell if it is a soil type that
starts with a number. Any ideas?

"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

Just disregard that alias name question. I see you're posting from a
different location so that answered my question.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
To you query yesterday 'about using alias name' I have send a mail which
bounced back..and hence checked..

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

If you mean , that's a bogus address. Spam that
address all you want!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Biff, is there a change in your email id from what is mentioned in the
profile..

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize
my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try
to
explain
the best that I can. I am trying to relate ranges of soil types to
their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell
C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of
those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as
I
enter a
soil type from the first range of soil types. Basically I need to
add
on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize
a
range
of cells...I know you can do that but I was getting an error when I
tried
to
highlight the entire range so I had to click each cell individually
to
get it
to work. Any tips or help would be greatly appreciated.

Sorry, typo. It enters a "0" if it is a soil type that starts with OR has a
letter in it.

"Keith - NRCS" wrote:

OK...I ran into another snag with it. It works great for the soil types that
start with a number but it enters a "0" in the cell if it is a soil type that
starts with a number. Any ideas?

"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try to
explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize a
range
of cells...I know you can do that but I was getting an error when I tried
to
highlight the entire range so I had to click each cell individually to
get it
to work. Any tips or help would be greatly appreciated.

Which formula did you end up using?

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Sorry, typo. It enters a "0" if it is a soil type that starts with OR has
a
letter in it.

"Keith - NRCS" wrote:

OK...I ran into another snag with it. It works great for the soil types
that
start with a number but it enters a "0" in the cell if it is a soil type
that
starts with a number. Any ideas?

"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize
my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try
to
explain
the best that I can. I am trying to relate ranges of soil types to
their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell
C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of
those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to
add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize
a
range
of cells...I know you can do that but I was getting an error when I
tried
to
highlight the entire range so I had to click each cell individually
to
get it
to work. Any tips or help would be greatly appreciated.

This one:

=LOOKUP(C11,$A$33:$A$104,$B$33:$B$104)

It worked great...and I actually stumbled onto it myself in the Help menu.
Thanks for the help!

"T. Valko" wrote:

Which formula did you end up using?

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Sorry, typo. It enters a "0" if it is a soil type that starts with OR has
a
letter in it.

"Keith - NRCS" wrote:

OK...I ran into another snag with it. It works great for the soil types
that
start with a number but it enters a "0" in the cell if it is a soil type
that
starts with a number. Any ideas?

"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Maybe I am over thinking this. Will it be easier to do if I organize
my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will try
to
explain
the best that I can. I am trying to relate ranges of soil types to
their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell
C11
(soil type) and it relates a corresponding dollar amount to it. The
problem I
am having is that I have 4 more ranges of cells and each range of
those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I
enter a
soil type from the first range of soil types. Basically I need to
add on
to
the formula above.

Also if there is a way to shorten the formula and have it recognize
a
range
of cells...I know you can do that but I was getting an error when I
tried
to
highlight the entire range so I had to click each cell individually
to
get it
to work. Any tips or help would be greatly appreciated.

Are you sure that formula works correctly? It requries the table data be
sorted in ascending order and is better suited for data that is all the same
data type.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
This one:

=LOOKUP(C11,$A$33:$A$104,$B$33:$B$104)

It worked great...and I actually stumbled onto it myself in the Help menu.
Thanks for the help!

"T. Valko" wrote:

Which formula did you end up using?

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in message
...
Sorry, typo. It enters a "0" if it is a soil type that starts with OR
has
a
letter in it.

"Keith - NRCS" wrote:

OK...I ran into another snag with it. It works great for the soil
types
that
start with a number but it enters a "0" in the cell if it is a soil
type
that
starts with a number. Any ideas?

"T. Valko" wrote:

Will it be easier...

Yes! See my reply in the other branch of this thread.

--
Biff
Microsoft Excel MVP


"Keith - NRCS" wrote in
message
...
Maybe I am over thinking this. Will it be easier to do if I
organize
my
data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

...etc. Soil type on the left and dollar amount on the right.

"Keith - NRCS" wrote:

I hope there is an easy answer to my formula problem so I will
try
to
explain
the best that I can. I am trying to relate ranges of soil types
to
their
corresponding dollar amounts.

Here is the formula I have working so far:
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11 =Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q 45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51 ,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into
cell
C11
(soil type) and it relates a corresponding dollar amount to it.
The
problem I
am having is that I have 4 more ranges of cells and each range of
those
cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long
as I
enter a
soil type from the first range of soil types. Basically I need to
add on
to
the formula above.

Also if there is a way to shorten the formula and have it
recognize
a
range
of cells...I know you can do that but I was getting an error when
I
tried
to
highlight the entire range so I had to click each cell
individually
to
get it
to work. Any tips or help would be greatly appreciated.