Displaying a range based on a cell's contents

[kim.braun]

I want a cell to display a range based on the information in another cell. For example, if A1 contains $5,678, I want B1 to show $5,001-$10,000; if A2 has $123,456, show $115,001-$130,000 in B2, etc. I have a list of the ranges that I need.

I am a pretty basic Excel user, and know I can do this with a long IF statement, but is there an easier, more "elegant" way to have Excel automatically display the range for me?

Thank you!

 

[L. Howard]

I want a cell to display a range based on the information in another cell. For example, if A1 contains $5,678, I want B1 to show $5,001-$10,000; if A2 has $123,456, show $115,001-$130,000 in B2, etc. I have a list of the ranges that I need.



I am a pretty basic Excel user, and know I can do this with a long IF statement, but is there an easier, more "elegant" way to have Excel automatically display the range for me?



Thank you!

Maybe something like this?

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Or Target.Cells.Count > 1 Then Exit Sub

Select Case Range("A1").Value

Case 5001 To 10000

Range("B2").Value = "$5,001-$10,000"

Case 115001 To 130000

Range("B2").Value = "$115001-$130,000"

Case Else

Range("B2").Value = 0

End Select
End Sub

Regards,
Howard

 

[joeu2004]

I want a cell to display a range based on the information
in another cell. For example, if A1 contains $5,678, I want
B1 to show $5,001-$10,000; if A2 has $123,456, show
$115,001-$130,000 in B2, etc. I have a list of the ranges
that I need.

Build a VLOOKUP table as follows.

Enter to lowest values into column C, starting with zero (assuming the
values in column A are never negative). For example:
C1: 0
C2: 5001
C3: 10001
C4: 20001
C5: 35001
C6: 55001
C7: 80001
C8: 115001
C9: 120001
C10: 130001
C11: 145001
C12: 165001
C13: 190001
C14: 220001

Enter the following formula into D1 and copy down through D13:
=D1 & "-" & D2-1

And in D14:
=D14 & "-"

Then enter the following formula into B1 and copy down column B:
=VLOOKUP(A1,$C$1:$D$D14,2)

 

[joeu2004]

Enter to lowest values into column C [....]
Enter the following formula into D1 and copy down through D13:
=D1 & "-" & D2-1

And in D14:
=D14 & "-"

The formulas in column D should be:
D1: =C1 & "-" & C2-1
D14: =C14 & "-"