Referencing cells

  • Thread starter Thread starter Ana.Amaya
  • Start date Start date
A

Ana.Amaya

Hello,

I need help in referencing cells I have a point system
that we are using for bonuses, for example:

1 review = 1 pt

2 review = 1. 50 pt.

so on and so forth. What I am trying to do is we have
columns where we put these numbers in like you put the 1
review in column A and then in column B you put the point
it equals. Is there any way of making it so where you can
type in the review number and then in the column B it will
automatically show the pt that review is worth? Thanks in
advance for your help!

Ana
 
Put this formula in B2 and copy down

=IF(A2="","",VLOOKUP(A2,{1,1;2,1.5;3,2;4,2.5;5,3},2,0))

edit the points part to fit your data, I used
1 = 1
2 = 1.5
3 = 2
4 = 2.5

and so on
 
The best way to approach this depends on how large your list is.

For example, if you only have 5 choices, you could use this:

=VLOOKUP(A1,{1,1;2,1.5;3,2;4,2.5;5,3},2,0)

Entered in B1 and copied down.

If your list is larger, perhaps 25 possibilities, you could enter it in an
out of the way location of your sheet, say Y1:Z25, with the review number in
Col Y and the points in Col Z.

Then you could enter this formula in B1 and copy down:

=IF(ISNA(MATCH(A1,Y1:Y25,0)),"",VLOOKUP(A1,$Y$1:$Z$25,2,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message Hello,

I need help in referencing cells I have a point system
that we are using for bonuses, for example:

1 review = 1 pt

2 review = 1. 50 pt.

so on and so forth. What I am trying to do is we have
columns where we put these numbers in like you put the 1
review in column A and then in column B you put the point
it equals. Is there any way of making it so where you can
type in the review number and then in the column B it will
automatically show the pt that review is worth? Thanks in
advance for your help!

Ana
 
Sorry !

Forgot the first set of absolutes.


=IF(ISNA(MATCH(A1,$Y$1:$Y$25,0)),"",VLOOKUP(A1,$Y$1:$Z$25,2,0))

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


The best way to approach this depends on how large your list is.

For example, if you only have 5 choices, you could use this:

=VLOOKUP(A1,{1,1;2,1.5;3,2;4,2.5;5,3},2,0)

Entered in B1 and copied down.

If your list is larger, perhaps 25 possibilities, you could enter it in an
out of the way location of your sheet, say Y1:Z25, with the review number in
Col Y and the points in Col Z.

Then you could enter this formula in B1 and copy down:

=IF(ISNA(MATCH(A1,Y1:Y25,0)),"",VLOOKUP(A1,$Y$1:$Z$25,2,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message Hello,

I need help in referencing cells I have a point system
that we are using for bonuses, for example:

1 review = 1 pt

2 review = 1. 50 pt.

so on and so forth. What I am trying to do is we have
columns where we put these numbers in like you put the 1
review in column A and then in column B you put the point
it equals. Is there any way of making it so where you can
type in the review number and then in the column B it will
automatically show the pt that review is worth? Thanks in
advance for your help!

Ana
 
Back
Top