Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
K
 
Posts: n/a
Default HOW CAN I PULL INFORMATION FROM ONE SHEET TO ANOTHER IF NOT #

I hope you can help!!

I need a formula that will take info from sheet A column K only if sheet A
column E is equal to sheet B column E. I need it to paste into Sheet
B column K exactly as shown in sheet A column K.

I tried using a Vlookup formula but I got #N/A and I am not sure if that is
because there is text as opposed to numbers or if I am using the wrong
function entirely.

Your help would be appreciated.

Thanks,
K

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default HOW CAN I PULL INFORMATION FROM ONE SHEET TO ANOTHER IF NOT #

Try this in K1 of Sheet B, assuming your data starts on row 1 in both
sheets:

=IF(ISNA(VLOOKUP(E1,'Sheet A'!E$1:E$100,1,0),"",VLOOKUP(E1,'Sheet
A'!E$1:K$100,7,0)

I have assumed you have 100 rows of data on Sheet A - adjust as
necessary. The formula can be copied down for as many items as you have
in Sheet B.

Hope this helps.

Pete

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
K
 
Posts: n/a
Default HOW CAN I PULL INFORMATION FROM ONE SHEET TO ANOTHER IF NOT #

It looks like it is looking for something to be entered where the quotation
marks are, what should I enter? Also, I forgot to mention in my original
message that the data that I need trasferred might be in row 300 in Sheet A
and I might need it in Row 125 in Sheet B. Does that matter?

I can't thank you enough for your help.

Thank you,

K

"Pete_UK" wrote:

Try this in K1 of Sheet B, assuming your data starts on row 1 in both
sheets:

=IF(ISNA(VLOOKUP(E1,'Sheet A'!E$1:E$100,1,0),"",VLOOKUP(E1,'Sheet
A'!E$1:K$100,7,0)

I have assumed you have 100 rows of data on Sheet A - adjust as
necessary. The formula can be copied down for as many items as you have
in Sheet B.

Hope this helps.

Pete


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default HOW CAN I PULL INFORMATION FROM ONE SHEET TO ANOTHER IF NOT #

The double quotes between the two commas indicate that a blank should
be returned if the value cannot be found in the search range - instead
of returning #NA it will show a blank.

If you have 300 data items in sheet A then you should make the ranges
'Sheet A'!E$1:E$300 and 'Sheet A'!E$1:K$300 - if you have more then
increase them further - these represent where you are trying to find a
match, so you choose the cell references to encompass all your data on
Sheet A.

The formula can be copied down to row 125 (or beyond). The cell
reference E1 will change to E125 if you copy the formula - if you only
want it on row 125 then type the formula into K125 with E125 instead of
E1 immediately after the open bracket.

Hope this helps.

Pete

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
K
 
Posts: n/a
Default HOW CAN I PULL INFORMATION FROM ONE SHEET TO ANOTHER IF NOT #

Hey Pete,

I have tried this many different ways and have not been able to get it to
work. Here is a specific example of what I am trying to do. I have a
worksheet called ED in which E35 matches E177 in another worksheet which is
entitled CP. I need what is in K35 to be input into K177 automatically. The
next item may be a match in E10 of ED and E25 in CP at which time I would
need the info in K10 of ED to input into K25 of CP.

Is this making any sense? Thanks again for your help!

K

"Pete_UK" wrote:

The double quotes between the two commas indicate that a blank should
be returned if the value cannot be found in the search range - instead
of returning #NA it will show a blank.

If you have 300 data items in sheet A then you should make the ranges
'Sheet A'!E$1:E$300 and 'Sheet A'!E$1:K$300 - if you have more then
increase them further - these represent where you are trying to find a
match, so you choose the cell references to encompass all your data on
Sheet A.

The formula can be copied down to row 125 (or beyond). The cell
reference E1 will change to E125 if you copy the formula - if you only
want it on row 125 then type the formula into K125 with E125 instead of
E1 immediately after the open bracket.

Hope this helps.

Pete


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Displaying information (contained in defined names) on a summary sheet, in different row numbers? [email protected] Excel Discussion (Misc queries) 0 May 15th 06 02:46 PM
Reference information from one sheet to a second sheet Kathleen New Users to Excel 2 March 23rd 06 09:49 PM
Saved Excel sheet - information disapearing djbeenie Excel Discussion (Misc queries) 5 December 30th 05 11:31 PM
Using a relative SHEET reference for source data in a chart James Charts and Charting in Excel 6 August 16th 05 05:07 PM
I need a daily sales sheet to pull from monthly figures sheet Draegen Excel Worksheet Functions 0 April 24th 05 05:58 AM


All times are GMT +1. The time now is 10:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"