Need your expertise.
I have a sheet1 with a table. Range A1:C20 has products, in the other columns I have some information about those products.
I have another sheet, which has range a1:c20 copied by sheet1. From D1:J20 I put other info about those products which change frequently.
Every now and then in Sheet1 changes are made, rows are inserted, deleted or products name change. So until now whenever I make changes I go to the other sheet and make those changes as well.
What I would like is somehow, don't know if it needs vba or can be done with formula is:
When changes are made to sheet 1 in a1:c20 I want them to be made to the other sheet as well.
I've tried the following which I found on the net and works, it does insert/delete the rows and make the changes once refreshed BUT only between a1:c20.
I want the rows to be inserted/deleted on the whole sheet so that the info I have already put in a product won't be at the wrong row.
1. Assume data on sheet1 is in range A5:B10. headings are in row 4
2. Select A4:B10 and assign it a name, say dummy
3. Select A4:B10 and press Ctrl+T to convert to a Table and save the file
4. Click on any cell in sheet2 and go to Data > From Other Sources > From Microsoft Query > Excel Files
5. Navigate to the folder where the file is saved, select the file and click on Next
6. click on the plus sign in the left hand side box, select the column which you want on sheet2 and click on the > Symbol
7. Click on Next twice
8. On the last screen, select "Return Data to MS Excel"
9. In the Import Data box, select Table and click on Finish
Of course, I know I could use Access instead of Excel but it'll take a long time for me to set up everything, I wonder if there is a way to do it in Excel since I've been using this workbook many years now.
Thank you in advance.
Bookmarks