Shorten formula

[troysie]

I am using the following formula to populate cells from another sheet.

Please Login or Register  to view this content.

Is there a way to shorten this formula as A1 will have a number from 1 to 10 and B1 to B10 will have different values or do I have to keep repeating the formula
Regards Troysie

[dilipandey]

Hi Troysie,


You can choose any one as per best fit:-

=IF(Sheet1!A1=1,Sheet1!B1,IF(Sheet1!A1=2,Sheet1!B2,0))

=IF(Sheet1!A1=1,Sheet1!B1,Sheet1!B2)

Regards,
DILIPandey

<click on below 'star' if this helps>

[Ron Coderre]

Try this....
=INDEX(Sheet1!B1:B10,Sheet1!A1)

Does that work for you?

[troysie]

Thanks to both of you but I think I prefer Rons solution
regards Troysie

[Marcol]

Or at a push ...

Please Login or Register  to view this content.

Remember this is volatile, better with Rons' INDEX() solution.