Formula to look up and copy

Audiojoe · Mar 19th, 2002, 03:47 AM

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

Dave Hawley · Mar 19th, 2002, 03:49 AM

Hi Audiojoe

Have you tried VLOOKUP?

Audiojoe · Mar 19th, 2002, 03:54 AM

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

Never mind, thanks anyhow

Derek · Mar 19th, 2002, 05:06 AM

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 ]

Audiojoe · Mar 19th, 2002, 05:22 AM

Cheers Derek,

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

Chris Davison · Mar 20th, 2002, 02:10 PM

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

Chris

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 03:47 AM	#1
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 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

Mar 19th, 2002, 03:49 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Audiojoe Have you tried VLOOKUP?

Mar 19th, 2002, 03:54 AM	#3
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Yeah, it's what we're using at the moment, but it's just so long winded Never mind, thanks anyhow

Mar 19th, 2002, 05:06 AM	#4
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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 ]

Mar 19th, 2002, 05:22 AM	#5
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Cheers Derek, I now have two methods and they both work great. Thanks for your input

Mar 20th, 2002, 02:10 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 Chris