Pull Data to a Tab based on the Tab Name

wangaa11

New Member
Joined
Feb 28, 2020
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
I would upload the file I am working on but I am not able to.

I am trying to figure out a formula that can pull certain information to a specific tab based on the tab name (can repeat the tab name in a column). I thought about a vlookup but it is going to have multiple answers as there could be multiple document names. This is a tracking file for out of date company policies and I want to organize them based on the departments. For example, I want to pull in specific columns, the name of the document, the expiry date, and the person responsible.

I would have my raw data in the data dumb tap and then I would have 5 tabs (Administration, Board, Executive, HR, Finance) from my data dump I want to bring over all the documents to the administrative tab and the table will look like this;

What would be the formula I would use to get the policy name based on it being Administration? Same for the review date and the reviewer

Policy NamePolicy Review DateDepartment Reviewer
Administration
Administration
Administration
Administration
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
are you still using version 2016 - i will have a look and see how to do that, if 365 then maybe filter()

365 version
=FILTER(DATA!A:E,DATA!E:E=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))

older versions
array - needs Control - Shift - Enter
=IFERROR(INDEX(DATA!A$2:A$10000,SMALL(IF(DATA!$E$2:$E$10000=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),ROW(A$2:A$10000)),ROW(1:1))-1,1),"")

Non array
just checked - aggregate introduced in 2016 version
=IFERROR(INDEX(DATA!A:A,AGGREGATE(15,6,ROW(DATA!$D$2:$D$1500)/(DATA!$E$2:$E$1500=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)),ROWS(A$2:A2))),"")
 
Upvote 0
are you still using version 2016 - i will have a look and see how to do that, if 365 then maybe filter()

365 version
=FILTER(DATA!A:E,DATA!E:E=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))

older versions
array - needs Control - Shift - Enter
=IFERROR(INDEX(DATA!A$2:A$10000,SMALL(IF(DATA!$E$2:$E$10000=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),ROW(A$2:A$10000)),ROW(1:1))-1,1),"")

Non array
just checked - aggregate introduced in 2016 version
=IFERROR(INDEX(DATA!A:A,AGGREGATE(15,6,ROW(DATA!$D$2:$D$1500)/(DATA!$E$2:$E$1500=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)),ROWS(A$2:A2))),"")
Thanks for responding to my post. It is office 2013 that I am working with. Can you just clarify what Cell and filename is referring to? I think I would need to use the IFERROR formula.
 
Upvote 0
your profile says 2016 , not 2013

cell is an excel function and filename is also excel to pull out the filename
explained by MS here
i do a mid , just to extract the sheetname we are on

open a blank spreadsheet
You need to save the spreasheet first with a name
put
=CELL("filename",A1)
anywhere - except A1
and you get the FULL path name of the file - so then you can get the sheet number

its easier in later versions of excel - with TEXTAFTER
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top