Extracting info automatically

Question

I work off 2 separate spreadsheets that are shared with live updates with a number of people. Some of the data from spreadsheet 1 is often needed to be transferred to spreadsheet 2 but I need to find a way to do this without copy and paste. Is there a way to put a code in that says "if cell y5 says yes transfer a copy to the other spreadsheet" and vice versa. I need the original to remain in spreadsheet 1 but this doesn't need updating from when transferred to spreadsheet 2

Answer 1

hi Nicola JOnes1

Sorry my reply was a bit late.

With your description, I tested the features you need on excel. Relying solely on function formulas in Excel may not achieve the function you need. Your functionality may need to be implemented via VBA.

I would very much like to help you, but there are issues with VBA code that are beyond the support of the forum. Therefore, I may not be able to give you more effective advice.

It is more suitable for publishing on Microsoft Learn, you can click on "Ask a question", there are experts who can provide more professional solutions in that place.  Here is a link to the forum where you can raise specific scenarios and share your idea to help solve the problem. Excel - Microsoft Q&AI won't be able to help you, but I'll leave that question open in case one of our amazing volunteers has ideas for you.

Thank you for your patience and understanding.

Best Regards,

LucyW-MSFT | Microsoft Community Support Specialist

Answer 2

Hello Lucy,

Thank you for your reply.

The spreadsheets contain a lot of personal information on people and they are often transferred from one location (spreadsheet 1) to another (spreadsheet 2) and occasionally back again. This information is just required to be kept for reporting purposes from the original spreadsheet. It will be specific columns that will need transferring and as the spreadsheets are not mirrored they will need to go into certain columns on spreadsheet 2. E.g. if column "x" says trousers then column "a" on spreadsheet 2 will populate to trousers. Then columns c-g, n-r and aa-ad will need to be transferred to columns b-f, o-t and ab-ae on spreadsheet 2.

I hope this makes sense.

Thanks

Nicola

Answer 3

Dear Nicola JOnes1

Welcome to Microsoft Community.

From your description, I understand the following information:

• You have two excel files, both of which are being shared to many people.
• You want to automatically copy and paste parts of the first table into the second table when a specific character (e.g. yes) appears in a specific cell (e.g. Y5) in the first table.

Due to the limited information I know, I may not be able to provide you with precise advice. If it is convenient for you, can you share with me more detailed information about your needs? <br><br> <br><ul><li><p data-prewrap="true">What is the range of content you want to copy and paste into the second table in the first table? Are there specific columns? (e.g. column A?) Or a specific number of rows? Or is it a specific area? Or is it an area where the position will change? </p><p data-prewrap="true"></p></li><li><p data-prewrap="true">"I need the original to remain in spreadsheet 1 but this doesn't need updating from when transferred to spreadsheet 2" Does this mean that you need to enter some information more than once in the first sheet and you want to keep all the information in the first sheet. In the second spreadsheet the later information will overwrite the previous information. </p><p data-prewrap="true"></p></li><li><p data-prewrap="true"><strong>To help you better, you can share your form with me via private message if it is convenient for you. I will then provide you with more reliable assistance based on the documents you share.</strong></p></li></ul><br><br><br>

Thank you for your patience and understanding.

We look forward to hearing from you!

Best Regards,

LucyW-MSFT | Microsoft Community Support Specialist