How to export data from different tabs in Excel

Closed

JSharon Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015 - 2 Mar 2015 à 14:45
JSharon Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015 - 3 Mar 2015 à 14:08

Hi,

I need help with a spreadsheet that has 4 tabs.

My tabs are:

Manager, Analyst_1, Analyst_2 and Analyst_3.

I need a formula that can transfer information from all the analysts to Manager. So, if a case number (which the title my columns B9) is entered and it matches with a case that is on any of the different "analyst", then the info on the others cells should be transferred.

I can do it with only one sheet but I haven't been able to find a formula to look through all three.

This is the formula that I'm using right now:

=IF(ISERROR(VLOOKUP($D$13,Analyst_1!$B$9:$D$118,2,FALSE)),"Not Found",VLOOKUP($D$13,Analyst_1!$B$9:$D$118,2,FALSE))

Can you please help me solve this?

Thanks a lot,

Sharon.

How to export data from different tabs in Excel
Export data from excel - Guide
How to copy data from one excel sheet to another - Guide
How to export data from facebook - Guide
Excel move data from one sheet to another - Guide
How to close tabs on android - Guide

1 response

Réponse 1 / 1

stan-star Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015 1
2 Mar 2015 à 16:31

You are on the right track IF there can only be one case# across all target sheets. If that is the case, you need to "Nest" the IF statements.

JSharon Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015
2 Mar 2015 à 17:06

Hi Stan-Star,

Thanks for your prompt reply, I have this now:
=IF(ISNA(VLOOKUP(D30,Analyst_1!B10:G118,2,0)),IF(ISNA(VLOOKUP(D30,Analyst_2!B10:G118,2,0)),VLOOKUP(D30,Analyst_3!B10:G118,2,0),VLOOKUP(D30,Analyst_2!B10:G118,2,0)),VLOOKUP(D30,Analyst_3!B10:G118,2,0))

This is working perfectly, the only problem is that I get a #N/A if there is no data on cell D30, where can I insert the error value to fix it?

THANK YOU!!

stan-star Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015 1 > JSharon Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015
2 Mar 2015 à 18:34

Try this :

=iferror(IF(ISNA(VLOOKUP(D30,Analyst_1!B10:G118,2,0)),IF(ISNA(VLOOKUP(D30,Analyst_2!B10:G118,2,0)),VLOOKUP(D30,Analyst_3!B10:G118,2,0),VLOOKUP(D30,Analyst_2!B10:G118,2,0)),VLOOKUP(D30,Analyst_3!B10:G118,2,0)),"")

This will simply set the result cell to blank. If you want to display some text, merely insert it between the quotes in my recommendation above.

JSharon Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015
3 Mar 2015 à 09:36

Thanks again Stan-Star, it works perfectly but is not retrieving data from Analyst_1, I tried to include this part "IF(ISNA(VLOOKUP(D30,Analyst_1!B10:G118,2,0))" after "=iferror(IF(ISNA(VLOOKUP(D30,Analyst_1!B10:G118,2,0))" but is not letting me do it. Can you help me? Sorry to keep bothering you and thanks a lot!!

Happy to assist,

OK, I re-wrote the formula utilizing Iferror as opposed to ISNA and IF . In my small simulation, it seems to work. Give it a try. Good Luck.

IFERROR(VLOOKUP(D30,Analyst_1!B12:G120,2,0),IFERROR(VLOOKUP(D30,Analyst_2!B12:G120,2,0),IFERROR(VLOOKUP(D30,Analyst_3!B12:G120,2,0),"Not Found")))

JSharon Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015 > stan-star Posts 4 Registration date Monday 2 March 2015 Status Member Last seen 3 March 2015
3 Mar 2015 à 14:08

Perfect!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Thanks a lot Stan-Star!!! You are the Best!!!

Similar discussions

Copying data from one Excel sheet to another.

How to extract data from one excel file to another

Unlock safe folder without losing any data

Extract data from one sheet to another

Similar discussions

Subscribe To Our Newsletter!