What the best function to use for auto copying values on another worksheet

[anshupillay]

Hi,
Im new here. I'm wondering what is the best function to use to auto copy data from one sheet to another.
Data is provided to me in columns with title however I need to copy data into a template which is not in the same column order. I want to automate copy function.
Im wondering what is the best quickest way to do this?
Do I need to build a macro,vba or name manger?

[FlameRetired]

Hi anshuplillay. Welcome to the forum.

My first thought is an INDEX/MATCH formula that can find and match appropriate columns. Without sample data this is just a guess.

The best way to resolve questions usually requires you to upload a representative Excel workbook file (not pics or screenshots ... saves retyping data) of what you are working with and what you want the output to be. It saves time and you often get faster and more response if you do.

Be sure to desensitize the data.

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[FDibbins]

1st, no formula can "copy" data to another location, formulas only work in the cell they reside in. Formulas only "pull" data, they cannot "push" answers.

2nd, you could put formulas where you want he data to show, probably something like FT suggested, but to be of more help, we will need to see some sample data

3rd, if you dont want formulas in the destination cells, then yes, you will need VBA for that.

[anshupillay]

thanks for your response.
Attached is sample data. Basically Data_Entry is what is received from the business. Data then needs to be copied on our own template for upload purpose. The upload needs to be in certain format, though all the information is provided on Data_Entry sheet we need to weed though the classification and copy paste data on different upload files.

What I want excel to do is pull data based on classification and dump on upload template.

Thanks in advance!

[JohnTopley]

In A2 of "Template_Employee"

=IFERROR(INDEX(Data_Entry!$B$3:$B$1000,SMALL(IF(Data_Entry!$A$3:$A$1000="EMPLOYEE",ROW(Data_Entry!$A$3:$A$1000)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

copy down


Change RED range to appropriate range for other columns then enter/copy down as above


For other template change GREEN highlighted

[anshupillay]

Hi John,
When I paste the formula I don't see the curly braces however the correct data is return.
But when I copy down other entries are not returned for EMPLOYEE.

Can you help please.

[anshupillay]

Hi John,
After few attempts I got the formula working! So happy.
However I need to add another criteria. ie only return where ABN (column D is blank).
Can you update the formula, it tried but couldn't get it right. Thanks

[sandy666]

you can try with PowerQuery (free add-in for Ex2010 Pro Plus from MS site)

Headers can be changed
criteria: ABN is blank
no formula, no vba

(it works only if PowerQuery is installed)