Linking 1 cell with others next to it

**number novice** · 11-22-2011, 06:46 PM

We have a program that outputs as per the attachment. The list of people is 500 names. I need to be able to using a list of 60 names extract each name and the information beside it. I am doing this manually at the moment however it just be comes tedious when a change is made and I have to go through it again. I thought of doing it with macros but some of the names have no information beside them and they are not evenly spaced in places, Also, when another person it inserted in the list, the macro loses the reference points. I have tried to use OFFSET but can't seem to get my head around it and LOOKUP and MATCH had me totally defeated. Is there a way of using IF to search for a name then have something else gather the information that pertains to that name. Hope I have made this easy to understand?

**rylo** · 11-28-2011, 01:22 AM

Hi

This does use a macro to reformat your source data into a shape that will allow formulas to bring in the relevant data.

Open the file, and run the macro aaa (in module 1). It has only been setup for Juliet / J Schedule so far, but you can expand the sheets it uses by including more details in the line

Please Login or Register  to view this content.

I've changed the formulas on juliet to use match / offset to bring back the data.

If this would work for you, then you could either use conditional formatting to color the cells, or use another macro to clear any existing color formatting, then input according to the result from the formulas.

Have a go and see if it would help.

rylo

**number novice** · 11-30-2011, 01:29 AM

OMG,

have just opened your reply and realised that I uploaded the wrong attachment. What I did upload was what I place the information into after I have sorted it. Find attached the file that I was supposed to submit. As you can see, with both files now there, I was hoping to be able to search through and pull out the matching names, with their associated information. I have been trying to do it as the "IF" command using the name of the person but then got beaten by how to attach the associated information with it. Then I was going to link it with the first file you have. So sorry for using up your valuable time. can you see anyway around what I am trying to do?

**rylo** · 11-30-2011, 08:55 PM

Hi

Open the attached file, then open the shift_j.xls file and make sure that shift_j is the active workbook.

Then run the macro doit. It isn't complete, but it should give you an idea on where I'm heading. See if this could be expanded to give you the result you are chasing.

Note that there are a a couple of time blocks in the data that I don't have a code to match based on your original file.

rylo

**number novice** · 12-02-2011, 05:07 PM

rylo,

You are a genius. I can't believe you got a macro to do all that. It is exactly what I was hoping to get. Will I be able to edit the name and time list for the lookup with out destroying anything? Is there a limit on the size of the range that they are contained in?