Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying information (contained in defined names) on a summary sheet, in different row numbers? | Excel Discussion (Misc queries) | |||
Reference information from one sheet to a second sheet | New Users to Excel | |||
Saved Excel sheet - information disapearing | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
I need a daily sales sheet to pull from monthly figures sheet | Excel Worksheet Functions |