Thanks:  0
Likes:  0

# Thread: Formula to look up and copy

1. Hiya,

I need a formula that will sit in a workbook called "John Smith" and look into another workbook called "Data". What it needs to do is look down column A in a sheet called "Raw" and when it finds the name "Abi Peshwar" it needs to show all the data that is next to her name, about 8 cells worth.

So basically in the workbook "Data", we have the data relating to "Abi Peshwar". It takes up a whole row. The thing is, the position of her details may change every day so it could be in row 12 one day and 70 the next day. What I need the "John Smith" workbook to do is look down Column A in the "Raw" sheet in the "Data" workbook, and when it finds "Abi Peshwar", to reproduce the entire row of data back in the "John Smith" workbook

Hope this is clear, if not tell me

2. Hi Audiojoe

Have you tried VLOOKUP?

3. Yeah, it's what we're using at the moment, but it's just so long winded

Never mind, thanks anyhow

4. Hi Audiojoe

Just an idea,
say Abi Peshwar is currently in A14 of
In workbook John Smith (in A1 or wherever) put the formula =[DATA.xls]Raw!A14 and scroll to the right. (basically just linking - this should work if Abi Peshwar is only moving up a down because of row inserts or deletes)

All the best
Derek

[ This Message was edited by: Derek on 2002-03-19 04:07 ]

[ This Message was edited by: Derek on 2002-03-19 04:07 ]

5. Cheers Derek,

I now have two methods and they both work great. Thanks for your input

6. Audiojoe,

if those 8 cells (say by 100 rows or whatever) are a named range (ie "nufc", the VLOOKUP formula is thusly :

and

and

etc etc

if this is still too longwinded, anchor the A1 as \$A1 and rather than copying across 8 times, substitue the 1,2,3,4 etc as "column()"

so if your first vlookup occurs in column A :

=VLOOKUP(\$A1,DATA.xls!nufc,column(),false)

and copy across

if your first vlookup occurs in column B :

=VLOOKUP(\$A1,DATA.xls!nufc,column()-1,false)

and copy across

or if your first vlookup occurs in column c :

=VLOOKUP(\$A1,DATA.xls!nufc,column()-2,false)

etc etc

in fact, you can copy the above formula and paste special unicode text into the cell in question then drag it across, changing the named range appropriately, if that's not too long-winded

Chris

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•