#16
|
|||
|
|||
Ms-Office 365
|
#17
|
||||
|
||||
In Sheet3 of the attached there's an additional table (green) based on the data in Sheet1 converted to a proper Excel table. All you need to do is update the table on Sheet1 ensuring all the data is in included in that table, then go to Sheet3, right-click the table and chose Refresh to update it. No formulas, no VBA.
|
#18
|
|||
|
|||
Hi thanks for help!!
I hope you have used the power query. May i know what function/query you have used to get Eng Hrs? So that i can append my data the same thanks again!! |
#19
|
||||
|
||||
Yes, it's Power Query; if you right-click on the green table, choose Table, Then Edit Query you'll be able to see the steps taken in the Query Editor.
To move the queries to another workbook, open the workbook I attached, go to the Data tab in the Ribbon, then in the Queries and Connections section, click on Queries and Connections. A side panel will appear. Do the same for the destination workbook. Go to the workbook I made and select both Queries (Table1 and fnProcess), right-click and choose Copy. Go to the destination workbook, right-click in the empty panel and choose Paste. An error will probably pop up saying it couldn't find Table1. At this point, go to your data,and select the 4 columns (Quarters, Member, Eng & Hrs) and select to the bottom of your data, then in the Ribbon, Insert tab, choose Table in the Tables section, double-check it's the corect range (and it does have headers) and click OK. If you're lucky, that new table will be called Table1, if so you should be able to go to the side panel, right-click the Table1 query and choose Refresh. If successful you should see nnn rows loaded under the query name and a new sheet will have been added also called Table1. If not, rename your source data table Table1. If you can't rename the table Table1, make a note of the table name, edit the Table1 query and change the first line from: = Excel.CurrentWorkbook(){[Name="Table1"]}[Content] by changing the table name in the double-quotes to match your table's name. Then when you Close and load, a new sheet should be added to your workbook with the new result table on it. That table you can cut and paste elsewhere. |
#20
|
|||
|
|||
Thanks for reply!!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
LOOKUP - Complex lookup with 2 lookups in 1 cell | sglandon | Excel | 6 | 05-05-2016 09:44 AM |
Lookup | Tony Singh | Excel | 3 | 03-06-2015 11:03 AM |
Lookup | angie.chang | Excel | 1 | 07-27-2012 09:45 PM |
Possible Lookup | Karen222 | Excel | 3 | 01-10-2012 05:41 AM |
LookUp | aztiguen24 | Excel | 5 | 05-24-2011 03:57 AM |