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.

Business Solution <tbody> </tbody>	Connect Status
Business Retail <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,4,0)
Business Digital <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,6,0)
Business Corporate <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,7,0)
Technology Retail <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,8,0)
Technology Corporate <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,9,0)

<tbody>
</tbody>

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

Business Solution <tbody> </tbody>	Connect Status
Business Retail <tbody> </tbody>	=VLOOKUP($B$11,Sep!C:J,4,0)
Business Digital <tbody> </tbody>	=VLOOKUP($B$11,Sep!C:J,6,0)
Business Corporate <tbody> </tbody>	=VLOOKUP($B$11,Sep!C:J,7,0)
Technology Retail <tbody> </tbody>	=VLOOKUP($B$11,Sep!C:J,8,0)
Technology Corporate <tbody> </tbody>	=VLOOKUP($B$11,Sep!C:J,9,0)

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 281px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Business Solution <tbody> </tbody>	Connect Status
Business Retail <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,4,0)
Business Digital <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,6,0)
Business Corporate <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,7,0)
Technology Retail <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,8,0)
Technology Corporate <tbody> </tbody>	=VLOOKUP($B$11,Oct!C:J,9,0)

<tbody>
</tbody>

</body>

 

[footoo]

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