Good morning,
Hopefully this is a simple solution.
I have a workbook with each tab (71 total) containing a business plan for a specific account. The account name is in cell A4 and the planned sales amount is in cell X9. I also have a worksheet in a different workbook with same account names in cells A2 - A72 and want to pull the corresponding sales from the first workbook into cells B2 - B72. Any ideas?
Many thanks in advance.
N
If your tabnames are the same as what you listed in A2:A79, then you can use the INDIRECT() function.... but if the original workbook is going to be closed, you will need to download and install a free addin called Morefunc.dll from here: http://xcell05.free.fr/english/ and use Indirect.Ext()
e.g. =Indirect.Ext("'C:\My Documents\[SalesWorkbook.xls]"&A2&"'!$X$9") copied down.
of course, replacing path and workbook name (in italics) with actual workbook path. Watch the single and double quotes... don't delete those.
Note: If you will keep the original book open, then Indirect() will do.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Honestly, I am not that familar with the INDIRECT function. The tab names are not exactly the same as the data in A2-A72.
I will look through the add in functions on that site and see if there is anything that can help me, but until then, I appreciate your assistance.
Thank you,
N
If you can manipulate, via formulas, the items in A2:A79 to end up the same as the tab names then you can still use the items in that range.
e.g. if Tab Name is Acct1 and your A2 value is Acct-1,,,then you can use a function such as =Substitute(A2,"=","") and get a result of Acct1 which would allow you to search the corresponding tab.....
...just FYI.... if you need further assistance, do come back.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
To get the Sheet names
In order to get the Names of the Sheets in any column as mentioned by NBVC, define A Name - Sheet and insert this formula in the Name define window..
Sheet - SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")
and then Lets say you want this to appear in the column A from A2 onwards then...
[ IF(ISERROR(INDEX(Sheets,ROWS($A$2:$A2)))," ",INDEX(Sheets,ROWS($A$2:$A2))) ]
Then you can use the IINDIRECT function..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks