Previous Topic Next Topic

How to export data from different tabs in Excel

Closed
JSharon Posts 4 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015 - Mar 2, 2015 at 02:45 PM
JSharon Posts 4 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015 - Mar 3, 2015 at 02:08 PM
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.

1 response

Answer 1 / 1
stan-star Posts 5 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015 1
Mar 2, 2015 at 04:31 PM
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.
1
JSharon Posts 4 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015
Mar 2, 2015 at 05:06 PM
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!!
0
stan-star Posts 5 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015 1 > JSharon Posts 4 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015
Mar 2, 2015 at 06:34 PM
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.
0
JSharon Posts 4 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015
Mar 3, 2015 at 09:36 AM
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!!
0
stan-star Posts 5 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015 1 > JSharon Posts 4 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015
Mar 3, 2015 at 11:22 AM
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")))
0
JSharon Posts 4 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015 > stan-star Posts 5 Registration date Monday March 2, 2015 Status Member Last seen March 3, 2015
Mar 3, 2015 at 02:08 PM
Perfect!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response