Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Trese
 
Posts: n/a
Default how do I copy info from one sheet to another via a formula?

Good day all, hope you can help!!

I need a formula that will take info from sheet A column b only if sheet A
column A contains "EMC" somewhere in the text. I need it to paste into Sheet
B coumn B exactly as shown in sheet A column b as well as index to the next
line that contains this info.

example:
Sheet A
A B
EMC, Safe PREPARE Checklist
ENVIR Send PCB for test
ID, EMC, SAFE Testing files complete

Sheet B:
A B
PREPARE Checklist
Testing files complete

I have tried using the following formula but I get empty lines "FALSE"
between the actual info and it is not pulling the information exactly as it
is on the other sheet. It is also not pulling all info from lines that
contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does not
pull that line item.
=IF(Sheet!A6="EMC",Sheet!B6)

Help, what am I doing wrong? I am a newbie and have spent several hours
looking through all the formulas/discussions you have provided to other users
but did not see one that would do what I am looking for on this sheet.
Your help would be appreciated.

Thanks,
Trese


  #2   Report Post  
Max
 
Posts: n/a
Default

One way which should do it ..

In SheetA
-----
Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") )

Copy D1 down to say, D100, to cover the max expected data range in col A
(can copy down ahead of expected data in col A)

In SheetB
----
Put in B1:

=IF(ISERROR(SMALL(SheetA!D:D,ROWS($A$1:A1))),"",IN DEX(SheetA!B:B,MATCH(SMALL
(SheetA!D:D,ROWS($A$1:A1)),SheetA!D:D,0)))

Copy B1 down to B100
(cover the same range size as in col D in SheetA)

Col B will return the desired results from SheetA's col B,
all neatly bunched at the top ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trese" wrote in message
...
Good day all, hope you can help!!

I need a formula that will take info from sheet A column b only if sheet A
column A contains "EMC" somewhere in the text. I need it to paste into

Sheet
B coumn B exactly as shown in sheet A column b as well as index to the

next
line that contains this info.

example:
Sheet A
A B
EMC, Safe PREPARE Checklist
ENVIR Send PCB for test
ID, EMC, SAFE Testing files complete

Sheet B:
A B
PREPARE Checklist
Testing files complete

I have tried using the following formula but I get empty lines "FALSE"
between the actual info and it is not pulling the information exactly as

it
is on the other sheet. It is also not pulling all info from lines that
contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does

not
pull that line item.
=IF(Sheet!A6="EMC",Sheet!B6)

Help, what am I doing wrong? I am a newbie and have spent several hours
looking through all the formulas/discussions you have provided to other

users
but did not see one that would do what I am looking for on this sheet.
Your help would be appreciated.

Thanks,
Trese




  #3   Report Post  
Trese
 
Posts: n/a
Default

Max,
Thanks for the quick response. I loaded these formulas into a sample sheet
but only had luck with the formula on sheet A. Sheet B formula did not pull
in any data. When I enter the formula EXCEL changes it and asks that I update
the file. Not sure why?
I can send you the sample sheet for reference if you like. Any ideas

Thanks,
Trese

"Max" wrote:

One way which should do it ..

In SheetA
-----
Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") )

Copy D1 down to say, D100, to cover the max expected data range in col A
(can copy down ahead of expected data in col A)

In SheetB
----
Put in B1:

=IF(ISERROR(SMALL(SheetA!D:D,ROWS($A$1:A1))),"",IN DEX(SheetA!B:B,MATCH(SMALL
(SheetA!D:D,ROWS($A$1:A1)),SheetA!D:D,0)))

Copy B1 down to B100
(cover the same range size as in col D in SheetA)

Col B will return the desired results from SheetA's col B,
all neatly bunched at the top ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trese" wrote in message
...
Good day all, hope you can help!!

I need a formula that will take info from sheet A column b only if sheet A
column A contains "EMC" somewhere in the text. I need it to paste into

Sheet
B coumn B exactly as shown in sheet A column b as well as index to the

next
line that contains this info.

example:
Sheet A
A B
EMC, Safe PREPARE Checklist
ENVIR Send PCB for test
ID, EMC, SAFE Testing files complete

Sheet B:
A B
PREPARE Checklist
Testing files complete

I have tried using the following formula but I get empty lines "FALSE"
between the actual info and it is not pulling the information exactly as

it
is on the other sheet. It is also not pulling all info from lines that
contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it does

not
pull that line item.
=IF(Sheet!A6="EMC",Sheet!B6)

Help, what am I doing wrong? I am a newbie and have spent several hours
looking through all the formulas/discussions you have provided to other

users
but did not see one that would do what I am looking for on this sheet.
Your help would be appreciated.

Thanks,
Trese





  #4   Report Post  
Max
 
Posts: n/a
Default

... Sheet B formula did not pull in any data. ..

You probably were hit by the line break(s)/wrap(s) in the formula when you
copied and pasted into SheetB. These break(s) need to be corrected manually
after pasting. The entire formula has to be in one line. Try it again. And
just in case needed,
here's a link to a sample file with the implemented construct:
http://savefile.com/files/6733373
Trese_wksht.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trese" wrote in message
...
Max,
Thanks for the quick response. I loaded these formulas into a sample sheet
but only had luck with the formula on sheet A. Sheet B formula did not

pull
in any data. When I enter the formula EXCEL changes it and asks that I

update
the file. Not sure why?
I can send you the sample sheet for reference if you like. Any ideas

Thanks,
Trese

"Max" wrote:

One way which should do it ..

In SheetA
-----
Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") )

Copy D1 down to say, D100, to cover the max expected data range in col A
(can copy down ahead of expected data in col A)

In SheetB
----
Put in B1:


=IF(ISERROR(SMALL(SheetA!D:D,ROWS($A$1:A1))),"",IN DEX(SheetA!B:B,MATCH(SMALL
(SheetA!D:D,ROWS($A$1:A1)),SheetA!D:D,0)))

Copy B1 down to B100
(cover the same range size as in col D in SheetA)

Col B will return the desired results from SheetA's col B,
all neatly bunched at the top ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trese" wrote in message
...
Good day all, hope you can help!!

I need a formula that will take info from sheet A column b only if

sheet A
column A contains "EMC" somewhere in the text. I need it to paste into

Sheet
B coumn B exactly as shown in sheet A column b as well as index to the

next
line that contains this info.

example:
Sheet A
A B
EMC, Safe PREPARE Checklist
ENVIR Send PCB for test
ID, EMC, SAFE Testing files complete

Sheet B:
A B
PREPARE Checklist
Testing files complete

I have tried using the following formula but I get empty lines "FALSE"
between the actual info and it is not pulling the information exactly

as
it
is on the other sheet. It is also not pulling all info from lines that
contain the EMC text. If EMC is listed as EMC, SAFE, ID, FW etc it

does
not
pull that line item.
=IF(Sheet!A6="EMC",Sheet!B6)

Help, what am I doing wrong? I am a newbie and have spent several

hours
looking through all the formulas/discussions you have provided to

other
users
but did not see one that would do what I am looking for on this sheet.
Your help would be appreciated.

Thanks,
Trese







  #5   Report Post  
Max
 
Posts: n/a
Default

In SheetA
-----
Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") )


Just some clarifications:

I presumed you wanted the search for "EMC" to be case sensitive. That's why
FIND was used in the above formula. If however, you need it to be
case-insensitive, just replace FIND with SEARCH in the formula.

And instead of hardcoding the target phrase "EMC" into the formula, we could
point the formula at an adjacent cell, say: E1, and enter the target phrase
in E1. This "softcoding" is useful if we have to do the same thing over for
other target phrases.

Then just put instead in D1:
=IF(A1="","",IF(ISNUMBER(FIND($E$1,A1)),ROW(),""))
and copy D1 down as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----




  #6   Report Post  
Trese
 
Posts: n/a
Default

Max,
IT WORKED!!!

I thought I corrected the line breaks but I guess I missed something the
first time around. Anyway- thanks for your time and your help- I REALLY
appreciate it.

Trese

"Max" wrote:

In SheetA
-----
Put in D1: =IF(A1="","",IF(ISNUMBER(FIND("EMC",A1)),ROW(),"") )


Just some clarifications:

I presumed you wanted the search for "EMC" to be case sensitive. That's why
FIND was used in the above formula. If however, you need it to be
case-insensitive, just replace FIND with SEARCH in the formula.

And instead of hardcoding the target phrase "EMC" into the formula, we could
point the formula at an adjacent cell, say: E1, and enter the target phrase
in E1. This "softcoding" is useful if we have to do the same thing over for
other target phrases.

Then just put instead in D1:
=IF(A1="","",IF(ISNUMBER(FIND($E$1,A1)),ROW(),""))
and copy D1 down as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----



  #7   Report Post  
Max
 
Posts: n/a
Default

Glad to hear that, Trese !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trese" wrote in message
...
Max,
IT WORKED!!!

I thought I corrected the line breaks but I guess I missed something the
first time around. Anyway- thanks for your time and your help- I REALLY
appreciate it.

Trese



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
I need to merge lots of info, w/o copy paste? JWS Wholesale Excel Worksheet Functions 0 July 18th 05 07:13 PM
copy formula referencing sheet name to another sheet Tat Excel Worksheet Functions 1 June 26th 05 03:00 AM
How do I copy Page Setups from one Sheet to Another sheet? Jeff Excel Worksheet Functions 1 June 12th 05 10:08 PM
Copy 1 Sheet to Another Dar Excel Worksheet Functions 3 June 6th 05 10:52 PM
Copy info from worksheet 1 to other sheets John Excel Worksheet Functions 2 February 12th 05 08:43 PM


All times are GMT +1. The time now is 02:43 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"