Linking data from sheet to sheet and VLOOKUP?

Hi

I am trying to link the results from one sheet to produce into another. With all the variables involved it is far too complex for me and I was hoping you could help.
I have attached a spreadsheet. The data in GOIT907 sheet has had a macro applied to clean it up and provide sheet references.

As a part solution, can/should a macro be created to fill a row with the Unit number ie move GOIT907 sheet reference B3 to appear in E4 and E5, then ref# B6 to appear in E7 and E8 etc. Would this help for the VLOOKUP? If so, can you provide me with a macro/solution? I think it would work if it looked for a unit code in the '1 Unit Database" and if found in Column B - keep reproducing the unit code in Column E, with the variable to stop when it finds the next occurance of a Unit Code, which would then restart the process with the new unit code? Does this make any sense?

Then, once the above 'clean up of the GOIT907' has been done,
Can the data in GOIT907, where a unit is shown in Column B (first one is RTC2209A) I want the result of CC in 2011 to appear in the corresponding section for Smith, Robert in his client sheet. The same for Jones, John. There are a number of units in column B, most appear in the individual client sheets (if they don’t I can readily add them).

I’m thinking some form of VLOOKUP with placement into corresponding sheets, but not sure.

Any help would be appreciated.
Thank you in anticipation.

Please see the attached file in my original message - it has been replaced with a more current version.

I've created a "Sample outcome SMITH" sheet to give you an idea of what I am trying to achieve.

The result data required as shown in the GOIT907 sheet (which has my preferred outcome from columns J to N (if you can create a formula/macro to take the data in A to D to replicate as shown in J to N that is my first problem. Once this is achieved, then....

(refer to 'Sample outcome SMITH' sheet). I would like the result data in GOIT907 to appear in the individual sheets for both Smith and Jones. The results need to appear in the same cell reference applicable to their individual sheets. For example on the GOIT907 sheet, Smith has a result of CC in RTC2209A in 2011.

The result data needs to match the unit reference on Smith’s sheet in Cell C2, then place the result of CC in Cell G2, and the year of 2011 in Cell H2. (Then do the same for Jones.)
This process would continue checking the GOIT907 sheet, applying results by unit to each client on their respective sheet.

This is what I think may be able to work, but I cant understand the sequence to make it happen, the concept is right, but I don't understand how it can work as it currently give me #REF. Can you help me with an outcome and explanation please.

Code Added

Please Login or Register  to view this content.

Once this is done, it would also be nice if (see the sample in outcome sheet ref Column F, specifically, F18, F21:F67), if no result was achieved yet a year was contained in the data, that “to be confirmed” message was automated in Column F (it works see F16). I'm sure an “IF” statement will work, and I have this (thanks to some research via this site) but it gives me "False" when both cells have nothing in them, I would prefer all False results to be blank. I tried ISNA, but it didn't like it.

Please Login or Register  to view this content.

For your reference and addition information: Client ID and Name appears in cells D71 & 73 respectively on the individual client sheet which can be matched (VLOOKUP) to the GOIT907 report, the unit data is also shown in the sheet named '1 Unit Database', it is used as the master link for unit reference/changes onto the individual client sheets, if this helps.

Thanks for your help, you guys are fantastic. I am trying to learn this stuff, but it is so complex.

Can anyone help me please, I realise you guys do this free service and time constraints apply for when you can get to it. But this is really doing my head in...... I'd really like to get it up and running so I can then produce full history on clients for the last 5 years - it is a huge job that will take me forever if I can't produce an automated file.

Hi Christopher,

Given below is the code to get the result in the GOIT907 sheet. Currently, it provides the output in a new sheet ("Sheet2"). Let me know if this needs to be changed. Kindly test the code and let me know if you get any errors.

Please Login or Register  to view this content.

I guess if this part works, a part of your huge problem will be solved. If it works fine, i can move ahead to the next part.

Hi Arlu, 1.5 out of 3 is very good mate.

As the Align data didn't work, I created a manual Sheet2 to run the update sheets.

The B_update_sheets works except not all results appear in the right cells. It is pasting the results from top in G and H 2 ok, but without any reference to the Unit code, it just populates the next result. So it works fine until a particular unit code is not represented in the Sheet2 report (GOIT907). If a client undertakes the first 6 units, not the 7th, but does 8, 9 and 10, the results populate for the first 9 units, it does not skip the 7th.

The "to be confirmed" is now working as well, thanks heaps for that.

We also have a problem with the A_Align_data script. I get the debug error, and when cancelled it has column A1 to E 1 highlighted. Is this because in the scripting it shows "A1:E1" Copy - I don't know very much but is this correct? Shouldn’t it be copying all of A:A,E:E so it copies the entire sheet or something like that? Apologies if I am wrong, just trying to find a solution.

This is a huge step forward, thanks very much for your input.

I intend to use this for a couple of years of information, from 2005 to 2011 and beyond. I could always add a column for each year in the individual sheets, then would it be easier to do a formula that looked from the clients sheet to the results sheet with multiple if function? So it would look for the unit code in the clients sheet, if it appears in the results sheet and there is a result place result in the cell. If it appears with no result it could show another result, if it doesn't appear, then it remains blank? Would this be easier than the scripting you've written?

Yes Arlu - 3 out of 3, for the TEST file I submitted. It was an Excellent outcome.

I amended the formula to look for a sheet named '2011', so for past and future years, creating and aligning will work easily - (this is brilliant stuff from you, thank you so much). However............

When I ran the "actual" report and the "aligning data formula", (the report has thousands of results), it doesn't align correctly. It is not always placing the unit in the correct place/sequence. Some of the Client Numbers are appearing in the Unit number reference and some results appear against the wrong unit/s. I've loaded an actual report with some "dummy" clients so you can see what happens. The 2011 sheet is what the "Align" macro created - you can see the problems there.

From my perspective, I'm wondering if it might be the inconsistency of where the unit number/s are placed in the report? It needs to look up and find the unit code directly above the last client ID and not recognise anything other than a Unit code (all relevant unit codes are listed in the '1 Unit Database' sheet). In the report I've noticed that in some cases there may be two unit codes in adjoining rows, but the one immediately above a client ID is required, and........ there may be lots of clients with results against the unit code which means any lookup or sequence will need to destinguish between a Unit Code and any other data.

My plan is to run the reports from 2007 to 2011 and beyond, starting from 2007. Then each year will override past results and year the result was obtained, so there will be no modifications needed to the location of "Results" and "Year" on the individual client sheets.

See my Comments below

I have been playing around with a macro and come up with the following:

Please Login or Register  to view this content.

I now have little work to do to get the sheet to contain only relevant data - this works fine now.
And when I run the B_Update on the TEST file it works perfectly.

Unfortunately - the B_Update_Sheets doesn't work on the "real" data sheets. It works in the testing file but in real time I get a debug error in the line shown below in your B_Update macro at

Please Login or Register  to view this content.

Not sure what the problem is but it is the very last line shown above that stops the process.
I have over 400 line items in the real sheet, with just over 200 client sheets (which are all named the same as in Column A)

Is there a way I can upload/send the real data (minus personal information) and have it deleted from the system?
Any suggestions, this is almost complete.

The REAL file has client sheets that may or may not have results, so the formula needs to skip over those without results - I'm not sure if that's an issue?