help with formulas for connecting 2 workbooks & connecting the sheets within it to sync new entries

[Amber1023]

i need help "linking" or connecting a sheet in 1 work book to 1 in another workbook
the first workbook is a excel sheet of microsoft forms responses with the exact same column headings as the form on my workbook i need it connected to. I need it to auto sync the new rows from the microsoft forms excel workbook to a 2023 field notes sheet then i have a few other sheets one of each area. So i would also like to make the columns that sync from the microsoft form response sheet to go to the correct sheet for each area 

i dont know if i should power query?

VLOOK up?

or set it up with Microsoft power automate 

I have 0 knowledge of using these features and have extremely basic formula and function knowledge. 

But i need this done ASAP. Please help tell me what to do & what formula to put in on each sheet or I can share it to you its easier to help put them in for me

[#2]

I would use PowerQuery to pull in the entire table form the form responses. Then I would use pivot tables (or more power query queries) to create the separate tabs. Note however, that refreshing the data will potentially zap any comments you add!

[#3]

isnt there just a way to link to workbooks together when one is updated the new updates post to the other workbook sheet?
or do I have to do it by coluns/row for new reponses?

i just want a copy that dosent get edited in case something goes wrong I still have the origonal input responses

[#4]

@Amber1023 

I second @Jan Karel Pieterse 's PowerQuery recommendation.

If you're looking to essentially pull all the form responses from another workbook (and have it update), PowerQuery is the way to go:

Import data from data sources (Power Query) - Microsoft Support

Excel can fetch the data easily with worksheet functions but when the data is in an external workbook it takes dynamic array functions off the table because the source would have to be open.

Another possible option is a PowerAutomate flow to move data from table to table but at that point you might as well go with the simpler approach in PQ and call it a day.

[#5]

have to do it by coluns/row for new reponses?

what do you mean new response?
After download from Ms Form and append the downloaded records to an exist worksheet?

[#6]

@Amber1023 

I don't think that's possible without restructuring of Notes file. In general, each new response is automatically added to Form Responses. In addition you may use Power Automate triggered on each new response which adds the record to this or that sheet into another file depends on response content. Not easy task, but possible.

With that in sheets shall be structured tables which has nothing but collected responses. 

Perhaps it's more productive to use only Form Responses file. Sheet with responses could be hided from end users, you may add another sheet with data preparations based on responses sheet, and finally reporting sheet.

Not sure that could be done fast. Depends on logic, what is desired output. IMHO, that's definitely doesn't work with Notes file as it is now.