Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract data from worksheet to worksheet
I am trying to extract data & text from one worksheet to place in a report on
another worksheet. I need if column a has a 27 in it, then I need to pick up the text in column K. Here's what I tried ="1"!sumif(a:a,27,k:k) Obviously, doesn't work. Also, there may be several 27's in column a, so I would need to pick up all the text in each related column K. Is this possible? Please don't refer me to a web site. I'm new at this and am having a hard time decifering formulas. I need someone to state the formula for me. Thanks... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract data from worksheet to worksheet
You won't find a formula to do this for you. Functions like Sum, Sumif etc.
work only on numbers, not on text. The standard way of doing what you want is to use Data Filter. You use the filter to display all the rows that have 27 in column A, then copy what's displayed. If you need more help, post back with the version of Excel you are using. By the way, what's wrong with web site referrals? They're often the best way of learning Excel. Regards, Fred. "Yoli" wrote in message ... I am trying to extract data & text from one worksheet to place in a report on another worksheet. I need if column a has a 27 in it, then I need to pick up the text in column K. Here's what I tried ="1"!sumif(a:a,27,k:k) Obviously, doesn't work. Also, there may be several 27's in column a, so I would need to pick up all the text in each related column K. Is this possible? Please don't refer me to a web site. I'm new at this and am having a hard time decifering formulas. I need someone to state the formula for me. Thanks... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract data from worksheet to worksheet
Single quotes, not double quotes, round the name of the source worksheet, so
if the name of your source worksheet is 1, then the formula would be ='1'!sumif(a:a,27,k:k) but that will sum numbers and won't concatenate text. -- David Biddulph "Yoli" wrote in message ... I am trying to extract data & text from one worksheet to place in a report on another worksheet. I need if column a has a 27 in it, then I need to pick up the text in column K. Here's what I tried ="1"!sumif(a:a,27,k:k) Obviously, doesn't work. Also, there may be several 27's in column a, so I would need to pick up all the text in each related column K. Is this possible? Please don't refer me to a web site. I'm new at this and am having a hard time decifering formulas. I need someone to state the formula for me. Thanks... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract data from worksheet to worksheet
Not really clear what you want so guessing hat on.
If you want a list of the text in col K if col A has 27 then try datafilterautofilterfilter col a for =27copy col K Record a macro to do it if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "Yoli" wrote in message ... I am trying to extract data & text from one worksheet to place in a report on another worksheet. I need if column a has a 27 in it, then I need to pick up the text in column K. Here's what I tried ="1"!sumif(a:a,27,k:k) Obviously, doesn't work. Also, there may be several 27's in column a, so I would need to pick up all the text in each related column K. Is this possible? Please don't refer me to a web site. I'm new at this and am having a hard time decifering formulas. I need someone to state the formula for me. Thanks... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract data from worksheet to worksheet
"Yoli" wrote: I am trying to extract data & text from one worksheet to place in a report on another worksheet. I need if column a has a 27 in it, then I need to pick up the text in column K. Here's what I tried ="1"!sumif(a:a,27,k:k) Obviously, doesn't work. Also, there may be several 27's in column a, so I would need to pick up all the text in each related column K. Is this possible? Please don't refer me to a web site. I'm new at this and am having a hard time decifering formulas. I need someone to state the formula for me. Thanks... This is excel 2003. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract data from worksheet to worksheet
This simple formulas play will deliver the goods
Assume your source data as described is in sheet: 1, with data from row2 down In the other sheet, In A2: =IF('1'!A2=27,ROW(),"") Leave A1 empty. This is your criteria col In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX('1'!K:K,SMALL(A :A,ROWS($1:1)))) Copy A2:B2 down to cover the max expected extent of source data. Minimize/hide col A. Col B will return the required results, all neatly packed at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Yoli" wrote: I am trying to extract data & text from one worksheet to place in a report on another worksheet. I need if column a has a 27 in it, then I need to pick up the text in column K. Here's what I tried ="1"!sumif(a:a,27,k:k) Obviously, doesn't work. Also, there may be several 27's in column a, so I would need to pick up all the text in each related column K. Is this possible? Please don't refer me to a web site. I'm new at this and am having a hard time decifering formulas. I need someone to state the formula for me. Thanks... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract data from worksheet to worksheet
Hi,
Since it sounds like you may want to put all the results in a single cell, the answer is you would need to write a custom VBA Function. Unfortunately, I'm off to work and can't do one for you right now, but it would look something like Function GetText(A as range, K as Range) as String dim cell as range dim t as string for each cell in A if cell=27 then t=t & cell next cell GetText = t End Function -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Yoli" wrote: I am trying to extract data & text from one worksheet to place in a report on another worksheet. I need if column a has a 27 in it, then I need to pick up the text in column K. Here's what I tried ="1"!sumif(a:a,27,k:k) Obviously, doesn't work. Also, there may be several 27's in column a, so I would need to pick up all the text in each related column K. Is this possible? Please don't refer me to a web site. I'm new at this and am having a hard time decifering formulas. I need someone to state the formula for me. Thanks... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract data from another worksheet | Excel Discussion (Misc queries) | |||
extract data from one worksheet to another worksheet | Excel Worksheet Functions | |||
Extract data from one worksheet | Excel Worksheet Functions | |||
Using a column of data from 1 worksheet to extract data from another worksheet | Excel Worksheet Functions | |||
extract data from worksheet | Excel Worksheet Functions |