I have a master spreadsheet that changes everytime it is updated, and it
contains 5 columns of information. In a new spreadsheet i want to be able to
search for one column for a specific bit of information, and when found it
will copy the whole row of information ? Is this possible ? Or do i need to
enter different formula's in each column of the new spreadsheet ? Any help
would be appreciated.

This could be done with a macro. If you are interested then if you provide me
with the following information then I'll write the code for you.

What is the worksheet name containing the data being searched?

What is the worksheet name to where the data is to be copied?

Is it always the same column to search? (If not, I can set it up so you will
need to select the column before running the macro.)

Am I correct in assuming that the row of data is to be appended to the
bottom of existing data in the worksheet to where it is being copied?

Does the row of data need to be deleted from its original location or only
copied?

What type of data is to be searched for in the column? (That is Numeric,
String or text, date etc). Also is the data being searched the entire cell
contents or only part of it. Example: Cell contains "My apple". Search for
'My apple' or search for part like 'apple'.

Do you know how to copy a macro into a workbook or would you need
instructions?

Do you know how to create a Command Button from the Control Toolbox toolbar?
(Not from the Forms toolbar).

What version of Excel are you using?

If you want the macro, then please ensure that you answer all questions.

--
Regards,

OssieMac


"gcouch" wrote:

I have a master spreadsheet that changes everytime it is updated, and it
contains 5 columns of information. In a new spreadsheet i want to be able to
search for one column for a specific bit of information, and when found it
will copy the whole row of information ? Is this possible ? Or do i need to
enter different formula's in each column of the new spreadsheet ? Any help
would be appreciated.

Forgot the most important question. What is the value that you want to search
for? or do you want the opportunity to enter it via an input box?


--
Regards,

OssieMac


"gcouch" wrote:

I have a master spreadsheet that changes everytime it is updated, and it
contains 5 columns of information. In a new spreadsheet i want to be able to
search for one column for a specific bit of information, and when found it
will copy the whole row of information ? Is this possible ? Or do i need to
enter different formula's in each column of the new spreadsheet ? Any help
would be appreciated.

Assuming your data looks like this:
1 A 7 G 13
2 B 8 H 14
3 C 9 I 15
4 D 10 J 16
5 E 11 K 17
6 F 12 L 18
and you want to search for "B" in column 2
and return the row, like this:
2 B 8 H 14
Name the search value MyVal,
the column number MyCol, and the data MyArray.
Select 5 cells and enter this array formula
with SHIFT+CTRL+ENTER
=INDEX(MyArray,MATCH(MyVal,INDEX(MyArray,,MyCol),0 ),)

"OssieMac" wrote:

This could be done with a macro. If you are interested then if you provide me
with the following information then I'll write the code for you.

What is the worksheet name containing the data being searched? Data.xls

What is the worksheet name to where the data is to be copied? FullGoods.xls

Is it always the same column to search? (If not, I can set it up so you will
need to select the column before running the macro.) It will always be Column A

Am I correct in assuming that the row of data is to be appended to the
bottom of existing data in the worksheet to where it is being copied? Ideally i would like it to overwrite the existing data where it is being copied, if possible

Does the row of data need to be deleted from its original location or only
copied? Only copied

What type of data is to be searched for in the column? (That is Numeric,
String or text, date etc). Also is the data being searched the entire cell
contents or only part of it. Example: Cell contains "My apple". Search for
'My apple' or search for part like 'apple'. It is numeric eg. 09107045. It would only need to search the first 4 numbers

Do you know how to copy a macro into a workbook or would you need
instructions? I think i do, but instructions would be good (just in case)

Do you know how to create a Command Button from the Control Toolbox toolbar?
(Not from the Forms toolbar). Yes

What version of Excel are you using? 2003

If you want the macro, then please ensure that you answer all questions.

--
Regards,

OssieMac


"gcouch" wrote:

I have a master spreadsheet that changes everytime it is updated, and it
contains 5 columns of information. In a new spreadsheet i want to be able to
search for one column for a specific bit of information, and when found it
will copy the whole row of information ? Is this possible ? Or do i need to
enter different formula's in each column of the new spreadsheet ? Any help
would be appreciated.

Thankyou, thats great, managed to get it to work. Is it possible to to copy
across more than one row, if say in your example, 'B' appeared twice in the
same column, but the data differed in the other cells ? I'm saying this as i
might look to include Best Before dates, so could end up with two different
dates for the same item, but would like to copy both. Thankyou again.

"Herbert Seidenberg" wrote:

Assuming your data looks like this:
1 A 7 G 13
2 B 8 H 14
3 C 9 I 15
4 D 10 J 16
5 E 11 K 17
6 F 12 L 18
and you want to search for "B" in column 2
and return the row, like this:
2 B 8 H 14
Name the search value MyVal,
the column number MyCol, and the data MyArray.
Select 5 cells and enter this array formula
with SHIFT+CTRL+ENTER
=INDEX(MyArray,MATCH(MyVal,INDEX(MyArray,,MyCol),0 ),)

Assume you have a "B" instead of an "E".
Select a 6 row by 5 column array and enter
this array formula:
=IF((INDEX(MyArray,,MyCol)=MyVal),MyArray,"")
It will give you this:

2 B 8 H 14


5 B 11 K 17

If you don't want the blank rows in between,
try Advanced Filter.
Label all the columns and add a criteria below col2:
col1 col2 col3 col4 col5
1 A 7 G 13
2 B 8 H 14
3 C 9 I 15
4 D 10 J 16
5 B 11 K 17
6 F 12 L 18
col2
B
Data Filter Advanced Filter Copy to another loc
List range: The data, including the column headers
Criteria range: The 2 additional cells in column 2
The "B" cell contains this formula
=MyVal
Copy to: Your choice
It will give you this:
col1 col2 col3 col4 col5
2 B 8 H 14
5 B 11 K 17