Data update loses alignment

[Krinsom]

Dear Chandoo, I've got a problem.

I have a worksheet which has hybrid columns.

The first three columns are daily automatically updated by a data conexion.

I have to fill manually the others columns data.

In the day after, when the data conexion brings new data to the worksheet, all the data typed manually the day before lose their line reference and the worksheet becomes useless.

Is there any way to keep the alignment without losing reliability?

Thank you in advance.


Krinsom

 

[Luke M]

Could you have 2 sheets? Think of sheet 1 as being static. It has the first 3 columns (not being updated) and this is also where you type the manual fill information. On Sheet 2, you have the updated information, and the following columns, which would normally be manually filled in, use VLOOKUP functions to get the manual filled in info from sheet 1. This is assuming that the info in first 3 columns has some sort of unique identifier, and the problem is that they keep getting rearranged.

 

[Krinsom]

Luke, in my original sheet the first three columns (automatically updated) contains the basic informations for the others columns which are manually typed, so I need to have the whole information in one single sheet.

The problem is when the sheet is updated with more data all the automatic data (former data) go to another row (or rows, it depends on the quantity of data income) and the manual typed data ramains unaligned... sad...

Any clue?

 

[Luke M]

Correct, that is what I understood. However, when you do the update, you need to have some sort of "key" that will let you re-align your data. Hence, the 2nd sheet. In step form


1. Update data

2. Fill in manual info

3. Copy all columns to to sheet 2

4. Re-update data

5. Create VLOOKUP formulas to pull data from the "key" on sheet 2 back to sheet 1.

6. Copy and Paste Values the formulas.

7. Change any manual info as needed.

8. Repeat steps 3-7 as required.