Excel Help! Locking data to row numbers??

Question

Hi,

I don't know if anyone can help me (I hope so), this is a little hard to explain, so please bare with me.

Is there a way I can keep certain data locked to certain row numbers? So when i paste over them the information still stays in place? 

So I have a spreadsheet that's got rows and rows of data, I run a report from issues people have put on the system, this generates a CSV and I paste the details over the top of previous data and it has a formula to tell me if anything has changed, ie: time/date/update/closed ect. (writing goes red) but sometimes extra rows get generated and overlaid on top of it and throws the rest of my rows and columns I manually put in, out of sync. They are in numerical order and the issues get generated with a reference number and sometimes, reference numbers get generated with numbers that are between the previously pasted numbers, (if this makes sense) Therefore knocking the manual columns I put in out of sync with what row they should be linked with, I know a solution is me going through and manually putting the info back in again the correct row, but that could take forever.

So my question is if one of you smart people can help and if there is a formula I can use to lock in the previous data into row numbers but being able to paste on top of it. So it can add it's own row as well? So say "issue reference" '24532' is locked to row 151 and all the data along the entire row is also locked to that row number, so if I paste on top of it excel knows not to move it but say "issue reference" '24531' then randomly turns up it insets its own row and doesn't move my previous data?

Please HELP!

Thank you in advance =)

p.s sorry for the essay!

Answer 1

You can either use VLOOKUP or, if you will be returning multiple columns of data for the same reference number, use MATCH to return the position of the relevant data on sheet 2 and then several INDEX formulas to pull the data.

So, let's say Sheet1 has the reference numbers starting in A2 and down, and SHeet2 has the reference numbers in column A and other data in columns B:H that you want returned to sheet1.

In say B2 on Sheet1, enter:

=MATCH(A2,Sheet2!A:A,0)

This will return the row number on sheet 2 where the reference number is located. Then in C2 you can enter:

=INDEX(Sheet2!B:B,$B2)

to return the relevant data from column B on sheet2. Copy this across and you will get the relevant data from column C, D and so on.

Does that help?

Answer 2

Thank you, I think we are going to go for the separate sheet method, now to work out a formula for the row (sheet1) to take the manual row information (sheet2) when all they will have in common is the reference number, if you could help with this that would be very helpful!

Thank you for the help,

Kind Regards

Patsy

Answer 3

You're not - that link is to this thread!

Unfortunately, I think you'd really be better off with a database application. If you're stuck with Excel, I'd suggest you use a separate sheet for the manual information, using the reference number as a key, and then your main sheet can simply look this information up using formulas. Otherwise you'd need some code to extract the data from the CSV, check each item to see if it matches an existing one, and if so populate that row, otherwise add it to the bottom of the list.

Answer 4

Hi,

I don't know if I am being dense but every time I click the link it just brings me back to this thread.

Patsy

Answer 5

Hello Jean,

Welcome to Microsoft Community.

To lock the previous data in rows check if the information provided in the following Microsoft Article helps:

https://answers.microsoft.com/en-us/office/forum/office_2010-excel/excel-help-locking-data-to-row-numbers/ca2469f4-6fb6-4573-804f-0a82c4e02d89

Hope the above information helps.

Thank you.