select cell a1 then link with cells b1:h13

[number novice]

Hi,

We have a program at work that outputs in Excel format, which is good but difficult to read in the format produced. I have got halfway through presenting a more readable version but have got stuck at the following. I need to search the output (400 names) for a name, then have the information to the right of that cell e.g. A1 be linked with a range of cells e.g. B1:H13 and be able to copy and paste to another sheet. A macro will work but if a new name is inserted, the cell references are pushed out of line. Any ideas?

[number novice]

OK, have been playing around with this and will try to explain my problem better. The command OFFSET uses a cell reference as a starting point, then by indicating how many cells down/up and across, you arrive at the solution. This only points to one cell however. What I am asking is, if it is possible to use a similar command to reference a block of cells 7 cells across by 10 cells deep and have all that information be copied to another workbook.

[Rahbee]

Just to clarify, you would like to search some array (your ex. B1:H13) for a value in A#. If that value is found, you would like the corresponding information pasted into another worksheet?

So, for example, if A1 matched one of the values in your array, you want the whole array pasted?

[number novice]

The search will be conducted against a value found in A# cells. To the right of these cells there is information connected with the value in the A# cell, that needs to be copied. The size of the information is 7 cells wide by 10 cells deep. If a new name is added to the A# cells and indexed, all the information cells move as well. Hope this explains it better. Have attached first lines of file to search.

[Rahbee]

How does this work?

It assumes a few things:
1) Data will always be inserted in Sheet1 in 8x10 starting in cell 'A1' (ex: A1:H10 increments)
2) You have no more than 1000 different names
3) The titles of the worksheets will remain the same (due to the fact that "=indirect" is combined with "=concatenate", thus using a text reference to the worksheet name)
4) No gaps will appear between 8x10 insertions in Sheet 1 (ex: Data 1=A1:H10, Data 2 = A11:H20, Data 3 = A21:H30, etc).

Sheet1 is all of the raw data
Sheet 2 is where you go to search for employees. In Sheet2, use the drop down menu in cell 'B2'. This list is populated as you enter new data in Sheet1

Again, all of this only works if all assumptions are met.

Hope this helps.

[number novice]

Rahbee,

Awesome piece of work! However, is there a way to have the name I am looking for in the formula? Then I just have to adjust for each person I am looking for, 60 out of 400, then have the information that goes with the person copied to a set location. Using the drop down list is great, but I was hoping to get away from the manual search and destroy aspect. Also, when I receive the raw data, there is a blank line in between each persons information. Am I hoping too much???

[Rahbee]

Thanks number novice. Making the change to include the 1 row between entries is very easy and I have already made it (see attached for revised sheet).

I am not sure I understand you other statement:

However, is there a way to have the name I am looking for in the formula? Then I just have to adjust for each person I am looking for, 60 out of 400, then have the information that goes with the person copied to a set location. Using the drop down list is great, but I was hoping to get away from the manual search and destroy aspect.

The name is essentially in the formula. When ever you add a new name to Sheet1, it is populated in a list in Sheet3 (a hidden worksheet). The drop down list was created with Excel's data validation to ensure that you do not having difficulty using data because of mis-spellings, mis-spellings, etc. For example, view the new name in the list, "Larson, Susan" in Sheet1. Go to Sheet2 and view the drop down menu. "Larson, Susan" is there. However, if you delete "Larson, Susan" from Sheet1, it no longer exists in Sheet3 and, thus, no longer exists in the drop down menu in Sheet 2.

I am not sure I follow your 60 out of 400 statement. Are you saying you want to search for 60 people at once? Or have 60 people on one sheet?

[number novice]

The 60 out 0f 400 hundred is I only deal with 60 people out of the list generated of 400 people. The "name in the formula" was I was angling for an "IF" formula. IF name = "Brown Caley", then select matching information that is associated with that name. That was what stumped me with selecting the information to be linked with the name. If the formula has the actual name, then if it needs to be changed for people coming and going (which doesn't happen hardly ever) it is no problem. It is the sorting through the 400 names and picking out the 60 I was aiming for. Still think it is possible? Really quick turn-around on the blank line problem. Thanks.

[number novice]

Hi, Tried playing around with the concatenate function which works great combining the information with the name, however I am trying to rid myself of searching through 400 names to pluck out 60 names with their information. Can concatenate be used with a IF command so that if a name is in a formula, "=IF Brown Caley" then grab the information associated with that name and insert into a set array size. If one name can be done, the I can just change the formula for each subsequent name and location.