Need a help with INDIRECT Function

**SMILE** · 06-12-2012, 03:44 AM

Hi
Attached is a file where in Sheet "MONTH" B1, I enter the worksheet name (Eg. "JAN"), so that I get the data from "JAN" sheet to "MONTH" sheet.
I need to fix formulas in B3:D5 using an INDIRECT function.
When I change the B1 value to "FEB", the Data to be copied from "FEB" sheet.

Can someone help me ?
Thanks
Tom

**buran** · 06-12-2012, 04:04 AM

Hi!
Put this formula in each cell of B3:D5 range

Please Login or Register  to view this content.

A bit of advise - I assume that the worksheet names will be all months in a year, i.e. JAN, FEB, MAR etc. i.e. fixed list. you may use data validation in cell B1 and have a drop-down list there to make it easier for the user.

Buran

**SMILE** · 06-12-2012, 04:07 AM

Thank you Buran, also thanks for the tip..
working well...

**buran** · 06-12-2012, 04:10 AM

I'm glad it worked for you :-)

**SMILE** · 06-12-2012, 04:33 AM

Hi
How would be the formula will be if I give a name for cell B1. For Eg: I define name for cell B1 as "PERIOD"
Could you help me to rewrite the above formula using the name instead of cell ref.
Thanks

**buran** · 06-12-2012, 04:37 AM

Please Login or Register  to view this content.

**SMILE** · 06-12-2012, 05:06 AM

Thanks again Buran...

**Marcol** · 06-12-2012, 07:05 AM

Robustness can is a problem when using INDIRECT()

Using named ranges helps, but these names unfortunately can't be dynamic

See this workbook

Try adding rows/columns before the tables in the worksheets
Also try dragging the tables to other locations on the sheet.

This isn't foolproof but should help you decide what options you might encounter.

It's, generally speaking, better to have your data on one sheet if possible.