Hi,
My primary workbook has a tab dedicated to listing holidays from our team resources from several countries. As this list comes from a separate workbook, I used the Power Query import data option to bring the data into my primary workbook.
Question 1-
Is that the best way to do it in Excel assuming I want to achieve something similar to what Google Sheets has named as "importrange" so that the data always syncs with the source from where it's pulling the source data from automatically?
I noticed you can also use the Copy/Paste Link functionality to try and sync your data with the source workbook, but I'm not sure this is the best way to do so.
Also, using or the other methods above make any difference once I move the two files to SharePoint to share with other people within the company? Will the Power Query continue to work on SharePoint or should I use the Copy/Paste Link functionality instead?
Anyways, as mentioned before I used Power Query and imported the holidays data from the source into my workbook, and that table now is loaded as a Power Query Table.
The table has the following columns under the Holidays tab:
[column A] | [column B] | [column C]
Date | Holiday | Country
Question 2 -
I have a formula on another tab which, based on a start and end dates determined by two distinct cells, has to find all holidays that fall between the two dates (from the Holidays tab) and group them by country in such a way that the output of the formula must display the number of total holidays per country for the specific date range in each row as follows:
(country) | (# days)
US | 2
Mexico | 3
Colombia | 1
As a reference, the formula I had on Google Sheets was using the Query Formula and looked like this:
=IFERROR(query('Holidays'!B3:D, "select D, count(D) where D is not null and date '" & text(C9, "yyyy-mm-dd") & "' < toDate(B) and toDate(B) < date '" & text(C10, "yyyy-mm-dd") & "' group by D label count(D) '' ", 0),"None")
Please help.
Thank you very much!
My primary workbook has a tab dedicated to listing holidays from our team resources from several countries. As this list comes from a separate workbook, I used the Power Query import data option to bring the data into my primary workbook.
Question 1-
Is that the best way to do it in Excel assuming I want to achieve something similar to what Google Sheets has named as "importrange" so that the data always syncs with the source from where it's pulling the source data from automatically?
I noticed you can also use the Copy/Paste Link functionality to try and sync your data with the source workbook, but I'm not sure this is the best way to do so.
Also, using or the other methods above make any difference once I move the two files to SharePoint to share with other people within the company? Will the Power Query continue to work on SharePoint or should I use the Copy/Paste Link functionality instead?
Anyways, as mentioned before I used Power Query and imported the holidays data from the source into my workbook, and that table now is loaded as a Power Query Table.
The table has the following columns under the Holidays tab:
[column A] | [column B] | [column C]
Date | Holiday | Country
Question 2 -
I have a formula on another tab which, based on a start and end dates determined by two distinct cells, has to find all holidays that fall between the two dates (from the Holidays tab) and group them by country in such a way that the output of the formula must display the number of total holidays per country for the specific date range in each row as follows:
(country) | (# days)
US | 2
Mexico | 3
Colombia | 1
As a reference, the formula I had on Google Sheets was using the Query Formula and looked like this:
=IFERROR(query('Holidays'!B3:D, "select D, count(D) where D is not null and date '" & text(C9, "yyyy-mm-dd") & "' < toDate(B) and toDate(B) < date '" & text(C10, "yyyy-mm-dd") & "' group by D label count(D) '' ", 0),"None")
Please help.
Thank you very much!
