Database type query but need answer in Excel

  Mark-335400 12:49 22 May 2003

Technically the answer to this problem would be via a database programme such Access, but as I have no knowledge (or time) I am looking at Excel to provide the answer.... I have a large spreadsheet consisting of about 10 columns wide that it is updated daily,,, what I need to be able to do is from within another workbook to have a cell that I can enter a six digit number which will then refer to the original large worksheet and find this same six digit number and then to copy the 9 adjacent column cell contents... any ideas ...Please... thanks in advance

  Taran 13:29 22 May 2003

Where do you want to copy the data to ?

Will it just be copied to the clipboard for you to manually paste into its destination or will it be dumped into the same destination file each time ?

  Mark-335400 14:31 22 May 2003

Taran, Thanks for responding, what I want to do is to be able to enter the six digit number in say any cell in column A in for example New Arivals..... it will then "search" workbook "on order" for the matching 6 digit number and then copy or extract the 9 cells on the ajacant right of the row and enter these in New arivals Basically I am trying to eliminate having to retype the information that is recorded in "on order" and entering the details in a seperate workbook called "new arrivals" In fact we have to reinput the same details in upto 5 different workbooks with the information that is contained in the "master" "on order" workbook. If there was a copy/search/extract function that would allow us to type in the six digit reference number that would then look into "on order" and copy the info connected to it within the cells along side it would dramatically reduce duplication and elimate input errors,,,,, Sorry about the waffle but sometimes it can be hard to explain a situation that you regard as the norm to a stranger... Thanks in advance

  Mark-335400 16:41 22 May 2003


  jazzypop 19:28 22 May 2003

Sounds like an ideal use for a macro, to me. See click here for one of many introdoctions to using macros that are available on the Web.

Simply setup a blank macro, press Record, carry out the actions you need to do, press Stop.

With a small amount of tinkering, I am sure that you can reduce this process that you describe to a 'one-click' operation.

  Mark-335400 21:31 22 May 2003

jazzypop, thanks for your reply, as the "database" is updated ongoing during the day I'm not sure the macro would work, I have set up a couple of simple macros but if I remember correctly they covered a predefined set of cells and would therefore not take into account the additional data being inputted during the day (I maybe wrong though),, I have been dabbling with the DGET function and LOOKUP function but I cant get my head around how to correctly set them up,, The DGET function looks promising as it would appear that I could be in one workbook and us this function to "look" into the master sheet and extract the information that matches but I have only ended up with eyestrain and a headache for my efforts... Please if any one could help in explaining how to access another work book and find or match a set of 6 digits (call it a part number) and then extract the information from the cells alongside this "part number" and copy them to this second workbook would be in my debt forever.... ?????????????

  Mark-335400 07:50 23 May 2003


  VoG™ 16:06 23 May 2003

You need to use VLOOKUP. Download this function dictionaty - it will tell you what to know click here

The sort of syntax you would use is

=VLOOKUP(A1, '[Book1]Sheet1'!$A1,$J$100, 2, FALSE)


A1 contains the key number that you want to look up,

'[Book1]Sheet1'!$A1,$J$100 is the range to look in for the key number AND the next 9 columns

2 is a culumn offset, so this formula would return the value from the column one to the right of the key number column. One column to the right of where you have the above formula you would use an offset of 3, and so on.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?