Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup change column index position - without changing formulae
I have a monthly report from a data warehouse which will have addditional
suppliers periodically reported alphabetically - thereby changing the col index no of some customers. For Example July I have 4 customers A, C, D and G so my work sheet is referring to say columns 1, 2, 3 and 4 in the lookup. I gain a customer B in August so in my lookup cust C has moved to col 3, D to col 4, and G to column 5 in the lookup. I still need the worksheet to pull the correct data for the relevant customer without rewriting the formulae each time there are changes - it's too manual and human error risks are high! To put this in perspective one of the reports I have vlookup formulae in runs from column A to column IJ - lots of formulae to move if i get a new customer whose name begins with an A! Any suggestions greatly appreciated! |
#2
|
|||
|
|||
Use a cell value. Say the cells are in row 1, then store the column id in
A10, and use a lookup such as =VLOOKUP(INDIRECT(A10&"1"),M1:P8,2,FALSE) Then all you do is plug the column id in A10. -- HTH RP (remove nothere from the email address if mailing direct) "loopyloobyloo" wrote in message ... I have a monthly report from a data warehouse which will have addditional suppliers periodically reported alphabetically - thereby changing the col index no of some customers. For Example July I have 4 customers A, C, D and G so my work sheet is referring to say columns 1, 2, 3 and 4 in the lookup. I gain a customer B in August so in my lookup cust C has moved to col 3, D to col 4, and G to column 5 in the lookup. I still need the worksheet to pull the correct data for the relevant customer without rewriting the formulae each time there are changes - it's too manual and human error risks are high! To put this in perspective one of the reports I have vlookup formulae in runs from column A to column IJ - lots of formulae to move if i get a new customer whose name begins with an A! Any suggestions greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I change the a,b,c, column headers in excel to names | Excel Discussion (Misc queries) | |||
Change the width of a single column in a column chart | Charts and Charting in Excel | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions | |||
Column Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |