Microsoft excel - Create a formula where data from different tabs/ Spreadsheets can be pupulated based on a cell on the spreadsheet

Question

Hello!

I have a spreadsheet multiple tabs with identical row/column names (eg: one tab with data for every major city in the U.S.)

What I am looking to do is add another tab to the excel document which can gather data from one of the major city tabs. 

If I wanted to gather data from column B (for example) in one of the tabs, is it possible to simply enter the tab name in a cell (or create a scroll down list)and incorporate that cell as part of the formula to specifiy which tab to gather data from.

Many thanks!

Answer 1

Thank you.

Would that also work with this formula?:

=IFERROR(INDEX('TAB NAME'!$B$2:$B$100,SMALL(IF(('TAB NAME'!$K$2:$K$100>=$B$3)*('TAB NAME'!$K$2:$K$100<=$B$4),ROW('TAB NAME'!$B$2:$B$100)-ROW('TAB NAME'!$B$2)+1),ROW(1:1))),"")

Answer 2

Hi,

Try this

=COUNTIFS(INDIRECT("'"&A1&"'!$K$2:$K$180"),">=1/1/2016",INDIRECT("'"&A1&"'!$K$2:$K$180"),"<=1/1/2017")

A1 has the tab name

Hope this helps.

Answer 3

Rather than specifying which tab to gather the data from, I want my formula to reference a cell with the same name as the tab name so that Excel knows which tab to reference.  For example,

=COUNTIFS('TAB NAME'!$K$2:$K$180,">=1/1/2016",'TAB NAME'!$K$2:$K$180,"<=1/1/2017")

Instead of typing the actual tab name in the formula, I want it to reference a cell where I can type the tab name myself.

This way, all I have to do is type the tab name in that cell and then I will have the specific data I am looking to gather.

Answer 4

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link? It will help me to give prompt and high quality solution.

Answer 5

Hi,

Please check the following link it may help you.

https://analysistabs.com/excel-vba/copy-data-fr...

if that don't works for you please share you Excel work on oneDrive and send me link so i can guide you in details

Kind Regards,

Engr. Jamshed Muhammad