Filling in multiple columns from a separate corresponding table

**knguyen777** · 08-29-2013, 02:07 PM

Hello all,

I am trying to insert a formula to populate a set of tables (columns B:I) based off of the data given in (columns Q:Y) so that all I will have to do is update the data in (columns Q:Y) instead of filling in each of the set of tables.

I have attached the sample.

Thank you in advance!

**jeffr27** · 08-29-2013, 03:04 PM

Will the names of the sales reps and managers change? or will the order of the names change? If not, you can use a direct reference. In cell B4 =R4 and so on.

**L-Drr** · 08-29-2013, 03:13 PM

Since you already have the right number of sales reps for each supervisor in your tables, this can be done with 3 steps:
1 find the supervisor's code in column Q (I think with a LOOKUP)
2 extract the column and row number of the first found supervisor code
3 use the OFFSET function to display a cell value in that area

problem is I don't know how to do step 2...
in B4:
step 1 would be something like =LOOKUP($B$3,Q:Q), which gives the "AT" from Q4
step 2 should result in "Q4"
step 3 would be something like =OFFSET(INDIRECT(resultofstep2),0,1) to give "LC" from R4

if you can figure out step 2 and can combine all 3 steps in one formula, you're set!