Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Adding a formula within a formula
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. |
#2
|
|||
|
|||
Adding a formula within a formula
Try with indirect function:
=VLOOKUP(ROW()-1,INDIRECT("Formulas!$"&A2&"$2:$BK$501"),A3,FALSE) "Jordan" wrote in message ... 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. |
#3
|
|||
|
|||
Adding a formula within a formula
"Jordan" wrote in message ... 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. |
#4
|
|||
|
|||
Adding a formula within a formula
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 "Jordan" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum total formula for adding 2 columnson time sheet | Excel Worksheet Functions | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
Formula for adding a comma in front of text in a cell | Excel Worksheet Functions | |||
Formula help : adding | New Users to Excel | |||
adding two sumproduct formulas together | Excel Worksheet Functions |