Vlookup Rows and Columns

[Lasantha]

Dear Team,

Kindly help me to vlookup details from “weekly client update template” to “CHSC” .

Please see the attached templates. I have given below vlookup criteria.

1. CHSC template H6 = weekly client tem: F20

2. CHSC template I6 = weekly client tem: F21

3. CHSC template K6 = weekly client tem: D13

4. CHSC template M6 = weekly client tem: F16

5. CHSC template N6 = weekly client tem: F17

6. CHSC template R6 = weekly client tem: D10

7. CHSC template S6 = weekly client tem: E10

8. CHSC template U6 = weekly client tem: D7

9. CHSC template V6 = weekly client tem: E7

10. CHSC template Y6 = weekly client tem: D3

11. CHSC template Z6 = weekly client tem: D27

12. CHSC template AA6 = weekly client tem: D4

13. CHSC template AB6 = weekly client tem: D5

14. CHSC template AL6 = weekly client tem: E6

Please let me know if you required further details.
Thank you for your time,
Lasantha.

 

[Chihiro]

With this sort of set up. There really isn't easy way of connecting via formula.

You may just have to suck it up and connect to each cell individually.

You should always start with flat table and only have cross tab structure as end result. For ease of analysis and reporting. I find that this to be most common mistake when people have issue generating report automatically/via formula.

As well, avoid use of merged cells (especially before the final product), it does nothing but add visual fluff.

 

[p45cal]

With this sort of set up. There really isn't easy way of connecting via formula.

You're not kidding!
In the attached, in cell H6 of the scorecard sheet there's a long formula:

=OFFSET(CHOOSE(COLUMN()-7,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$C$19,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$C$20,,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$A$12,,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$C$15,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$C$16,,,,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$A$9,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$B$9,,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$A$6,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$B$6,,,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$A$2,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$A$26,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$A$3,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$A$4,,,,,,,,,,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$B$5),MATCH($C6,'C:\Users\Public\Documents\[Weekly Client Update_Template.xlsx]Agent 1'!$D$2:$D$400,0),3)

This formula can be copied down and across to cells H6:I15,M6:N15,R6:S15,U6:V15,Y6:AB15,K6:K15,AL6:AL15.
It won't update the values without the Weekly Client Update_Template.xlsx being open too.
When you first open the attached it should ask you to update/edit links, at which point you need to point that link at your equivalent file. I only had fair success at doing this and ended up with a lot of #REFs in the formulae, but a search and replace of #REF to Agenda 1 in the appropriate cells soon had it working again.
I did try with named ranges and INDEX and OFFSET but the named ranges insisted on converting the likes of ,$C$15,$C$16, to ,$C$15:$C$16, which was no good for the area number agument of INDEX.
Anyway, I wouldn't be strongly recommending the use of this type of formula, it's too difficult to maintain and any changes to the positions of data on either of the sheets could lead to re-write of the formula. The only thing going for it is that it's the same formula for all the cells concerned.

 

[David Evans]

With this sort of set up. There really isn't easy way of connecting via formula.

You may just have to suck it up and connect to each cell individually.

You should always start with flat table and only have cross tab structure as end result. For ease of analysis and reporting. I find that this to be most common mistake when people have issue generating report automatically/via formula.

As well, avoid use of merged cells (especially before the final product), it does nothing but add visual fluff and cause unusual problems.

As usual @Chihiro is on the money, although I've taken the liberty of adding to it ...

I like your tern visual fluff - I'd like to use it with appropriate attribution

 

[Lasantha]

Dear @p45cal ,

Thank you very much for this. you are awesome. this is really helpful.

Lasantha.