INDIRECT reference

  • Thread starter Thread starter saturnin02
  • Start date Start date
S

saturnin02

Hi. I need to have in say cell A1 a drop down list (which I obtain with the
data validation tool) which shows a list like 0%, 5%, 10%, 15%, etc. so that
people looking at the spreasheet can select their % desired. BUT, while I
would like the numbers to chosse from in the drop down list to be those
above, I really need them to be divided by 12 i.e. (0%/12), then (5%/12),
then (10%/12), etc.
I thought i cd try to do it with the indirect function but I ma not really
sure what the best way to do it. Is?
Any suggestions?
I made be going about it the wrong way as well....
Tx,
Sat
 
I cd but that is only part of the formula.
But for other reasons, I'd liek to know if there is a way of doing what I
posted below--something like ti I mean....
 
Hi saturnin02!

My very strong preference is to go the way of Bob and allow APRs to be
entered and to do the adjustment to the monthly effective rate in
another cell that is clearly labeled that way. But you've rejected
that approach.

Here's one way and although I haven't thoroughly test it, it does seem
to do the job. The principle is to include the set schedule of rates
plus those rate divided by 12 in your data validated list and then use
a worksheet_change event handler.

It is subject to a restriction that the lowest rate that you have in
your unadjusted list must not be greater than the highest unadjusted
rate / 12. I can allow as a special case a rate of 0%.

I have a range of cells

H1:H13 has rates 15% down to 3%
H14 has 0%

H15:
=ROUND(H1/12,6)
Copied down to H27

In A1 I have my data validated entry which relies on a list

=$H$1:$H$27

Then I have a Worksheet_Change event handling routine:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = True
End
End If
If Range("A1").Value = 0 Then
Application.EnableEvents = True
End
End If
If Range("A1").Value < 0.02 Then
Application.EnableEvents = True
End
End If
Range("A1").Value = Round(Range("A1").Value / 12, 6)
Application.EnableEvents = True
End Sub


It's probably capable of being improved upon so hang around and watch
for other comments / replies.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs’ Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Possibly a shorter way would be to create a named formula

=sheet1!$A$1/12

Insert|Name|Define

Then just enter the name and the formula

Just refer to the name rather than a cell in your calculations. (make it
short and easy to remember)

HTH

PC
 
Back
Top