Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 08-26-2021, 06:02 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default


Ms-Office 365
Reply With Quote
  #17  
Old 08-26-2021, 10:01 AM
p45cal p45cal is offline Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 514
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

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.
Attached Files
File Type: xlsx msofficeforums47554TextjoinPivotsAndPQ.xlsx (49.9 KB, 1 views)
Reply With Quote
  #18  
Old 08-26-2021, 09:01 PM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

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!!
Reply With Quote
  #19  
Old 08-27-2021, 04:11 AM
p45cal p45cal is offline Lookup and Textjoin help Windows 10 Lookup and Textjoin help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 514
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

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.
Reply With Quote
  #20  
Old 09-02-2021, 12:48 AM
ganesang ganesang is offline Lookup and Textjoin help Windows XP Lookup and Textjoin help Office 2016
Competent Performer
Lookup and Textjoin help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Thanks for reply!!
Reply With Quote
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 and Textjoin help Lookup Tony Singh Excel 3 03-06-2015 11:03 AM
Lookup angie.chang Excel 1 07-27-2012 09:45 PM
Lookup and Textjoin help Possible Lookup Karen222 Excel 3 01-10-2012 05:41 AM
Lookup and Textjoin help LookUp aztiguen24 Excel 5 05-24-2011 03:57 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:10 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft