select data from different sheets

[satishkulkarni]

Hi,

Thanks in advance.

I have data stored for 10 locations, for each day in 12 monthly sheets. Now I want to pick up data for particular date in a year in report sheet.


Depending on month I could not fetch data. I am not able to give sheet name(march in following example) through formula.

=index(march!b1:af10,match(location_name,anymonthsheet!a1:a10,0-Exact match),day)


satish

 

[Hui]

Satish

You will need to incorporate the Indirect function in your formula

Indirect takes Text and converts it to a fully structured range reference


eg: If

A1 = Book2.xlsx

A2 = Sheet1

A3 = a1

=Indirect("[ & A1 & ]" & A2 & "!" & A3)

is the same as

=INDIRECT("[book2.xlsx]sheet1!A1")


you only need to include the Workbook or Sheet if you are referring to external Workbooks or other sheets as relevant


So in your example

=index(march!b1:af10,match(location_name,anymonthsheet!a1:a10,0),day)

could become

=index(Indirect(A1&"!b1:af10",match(location_name,Indirect("anymonthsheet"&"!a1:a10",0),day)

 

[SirJB7]

Hi, satiskulkarni!


Give a look at this post, I've just finish writing on it, and it uses the technique described by Hui.

http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab


Regards!

 

[satishkulkarni]

Thanks Hui Sir and SirJB7. I have tried lot but I have not used indirect function rather I had no idea about how Indirect function can be used. Thank you very much.

 

[SirJB7]

Hi, satishkulkarni!


Considered uploading your workbook?


Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!

 

[satishkulkarni]

Sorry Sir! I should have mentioned that problem is solved. I have written that I had no idea ...... is my lack of knowledge about function Indirect, before getting your help.


Thank you.

 

[SirJB7]

Hi, satishkulkarni!


Well, glad you could solve it... even you don't fully understand how it works.

If the only problem is INDIRECT function, you can try this:

a) press the "f(x)" symbol at left of edit bar, choose select a cell that has that function, category Search & Referece, then INDIRECT, and press the left bottom link "Help about this function": you'll find there all the information that Excel provides for it

b) from a cell that has the INDIRECT function, press the "f(x)" symbol and check what parameters is it receiving and compare them with the notes you should have written down in the previous point


As my two cents, let me say that indirect function displays the value of the cell reference which it contents is referenced as argument:

A1 has the value "9" (unquoted, numeric), A2 has the value "A1" (unquoted, but text, not as formula =A1), A3 has the formula =INDIRECT(A2), then A3 displays 9.


Regards!


PS: Welcome back whenever needed or wanted.

 

[satishkulkarni]

Hi SirJB7,


Now, I have correctly understood Indirect Function. Thank you very much.


Regards.


satish

 

[SirJB7]

Hi, satishkulkarni!

Thanks for the feedback and happy to have helped you. Keep on coming back.

Regards!