Adding a formula within a formula

[Guest]

I am using the following formula:
=VLOOKUP(ROW()-1,Formulas!$D$2:$BK$501,51,FALSE)

I'm trying to figure out a way to not hardcode the column letters and the
col_indx_num. I have added two rows to each sheet with the information I
need. If the letter D is in A2 and the number 51 is in A3 how would I write
that into my formula?

Detailed information below - As a better way to do this would be appreciated.

I have to copy this formula into 10 columns for each sheet and I have 50
sheets.

Each time I copy it on the same sheet form column to column I have to change
the col_indx_num and then when I copy it to a new sheet the col_indx_num
changes for each column by -1. I also have to change the $D to $E and so
forth.

On my formulas sheet I have information setup so each sheet does it's look
up starting with the left most column first from column D for sheet1 and then
from column E for sheet2. The information it returns are all in the same
column but because I am changing the first or left most column it changes the
col_indx_num

Very time consuming. Any help will be appreciated.

 

[Mladen_Dj]

Try with indirect function:

=VLOOKUP(ROW()-1,INDIRECT("Formulas!$"&A2&"$2:$BK$501"),A3,FALSE)

 

[Bob Phillips]

I am using the following formula:
=VLOOKUP(ROW()-1,Formulas!$D$2:$BK$501,51,FALSE)

I'm trying to figure out a way to not hardcode the column letters and the
col_indx_num. I have added two rows to each sheet with the information I
need. If the letter D is in A2 and the number 51 is in A3 how would I write
that into my formula?
=VLOOKUP(ROW()-1,INDIRECT("Formulas!$"&A2&"$2:$BK$501"),A3,FALSE)


Detailed information below - As a better way to do this would be appreciated.

I have to copy this formula into 10 columns for each sheet and I have 50
sheets.

Each time I copy it on the same sheet form column to column I have to change
the col_indx_num and then when I copy it to a new sheet the col_indx_num
changes for each column by -1. I also have to change the $D to $E and so
forth.

On my formulas sheet I have information setup so each sheet does it's look
up starting with the left most column first from column D for sheet1 and then
from column E for sheet2. The information it returns are all in the same
column but because I am changing the first or left most column it changes the
col_indx_num

Very time consuming. Any help will be appreciated.

Try this method first, and see if it is any easier.

 

[Guest]

Referencing cell A3 instead of the number 51 is simple: just change the 51
to $A$3. As for the table_range, I'd suggest defining a name and referencing
the range by name instead of cobbling up a formula to create the reference.
Highlight the table range, then Insert > Names > Define and choose the name.
Then your vlookup becomes =vlookup(row()-1,Your_Chosen_Name,$A$3,false).
When the range changes, update the name's meaning and you're all set.
--Bruce