Macro to change VLookup Range

[RahulNa]

Hi Guys,

I want to update Vlookup range in the report with respect to sheet name in cell A27.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Solution[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Connect Status[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Retail[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,4,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Digital[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,6,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Corporate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,7,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Technology Retail[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,8,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Technology Corporate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,9,0)[/TD]
[/TR]
</tbody>[/TABLE]

For eg:- Cell A27 have drop down with Aug,Sep,Oct. So if I select Sep then the whole range should change as


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Solution[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Connect Status[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Retail[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Sep!C:J,4,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Digital[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Sep!C:J,6,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Corporate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Sep!C:J,7,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Technology Retail[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Sep!C:J,8,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Technology Corporate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Sep!C:J,9,0)

[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 281px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Solution[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Connect Status[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Retail[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,4,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Digital[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,6,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Business Corporate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,7,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Technology Retail[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,8,0)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Technology Corporate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=VLOOKUP($B$11,Oct!C:J,9,0)

[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[footoo]

=vlookup($b$11,indirect($a$27 &"!c:j"),4,0)