Extract data from worksheet to worksheet

[Yoli]

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...

 

[Fred Smith]

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.

 

[David Biddulph]

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.

 

[Don Guillett]

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
data>filter>autofilter>filter col a for =27>copy col K
Record a macro to do it if desired.

 

[Yoli]

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.

 

[Max]

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

 

[Shane Devenshire]

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