Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Question about transferring data between workbooks.

Copper Contributor

Hello,

 

First time posting a question on here, so thank you in advance for any responses.

 

I've recently started using Excel more as a tool at work, to improve efficiency in what we do.

 

I've had an idea for a way I'd like to develop some workbooks, and would like to know whether there are any features in Excel that would enable me to achieve said idea.

 

I would like to add a drop down selection box on the end of each row of data, with one of the options basically being "proceed to next stage" (or similar). I would like certain pieces of data contained within that row, to automatically populate onto a new row in a separate workbook - if the "proceed to next stage" dropdown option is selected.

 

If possible, I would also like to be able to reverse this process, so if I change my dropdown option on the end of a data row from "proceed to next stage" to "do not proceed" (for example), then the data line in the separate workbook would be removed.

 

Are there any features which would allow me to do this, or similar, and if so could somebody please point me in that direction so I can look up more information online.

 

Thank you.

2 Replies
The drop-down is easily achieved using Data, Validation, List option.
I would also format the table as table, this ensures that last row always gets the drop-down.

The transfer of data I would do from the other workbook: Insert a sheet where the data must land, then choose Data, Get Data, From file, From Workbook. After clicking on the table name, click Transform data. In the next pane you can filter for that last column and delete any columns you don't need and rearrange them in the order you want them to appear. Then click Close & Load and tell it where to place that data. In the properties you can set that table to automatically refresh when the workbook is opened.

@Jan Karel Pieterse 

 

Excellent. This sounds like exactly what I'm looking for, thanks a lot. I'm going to go away and give it a go, and will report back.