Excel question

[gtopmafia]

Is there a way to merge in a formula the column reference for example B and then a row reference based on a number in a cell.

To clarify this is what I mean

I want the formula to do something like this

vlookup(a1, b1:c10,2,0)


where I want to get B1:B10 by doing something like

vlookup(a1, B&d1:B&e1,2, 0)

where d1 = 1
where e1 = 10

The reason is I am having to build a payment schedule for for multiple users from a loan type thing. The existing model is a mess and we dont have the time to rebuild it. Im trying to reduce the amount of repetitive behavior and monotony of dragging down or manually changing the numbers in the formula. I know the difference between each is 21 and so I have easy made a column which includes these numbers for example 200,221,242 etc. So I want to see if there is a way I can merge this into the formula so I can just copy the formula down.

Thanks

 

[dreid1011]

You can use INDIRECT() to construct a string for the range. Like so:

INDIRECT("B"&C1)

For the full range it would be like this:

INDIRECT("B"&C1&":B"&C2)

Where C2 might contain the number 10 or whatever last row you want.

 

[gtopmafia]

You Sir are a lifesaver!

 

[dreid1011]

You're welcome. One note about INDRECT; it is a volatile function so if you are going to be using it many times it can affect the performance of your workbook as it recalculates anytime the worksheet changes.