Formula to look up and copy

Audiojoe

Active Member
Joined
Feb 20, 2002
Messages
285
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Yeah, it's what we're using at the moment, but it's just so long winded

Never mind, thanks anyhow
 
Upvote 0
Hi Audiojoe

Just an idea,
say Abi Peshwar is currently in A14 of
your workbook Data.
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
 
Upvote 0
Audiojoe,

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

=VLOOKUP(A1,DATA.xls!nufc,1,false) in your first column

and

=VLOOKUP(A1,DATA.xls!nufc,2,false) in your second column

and

=VLOOKUP(A1,DATA.xls!nufc,3,false) in your third column

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

:wink:

Chris
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top