MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- Office Version
- 2013
- Platform
- Windows
I have an Excel workbook that uses Power Query From Web to populate one sheet that then populates another sheet based on the refresh from the web.
The webpage I get data from is 12 months of exchange data.
So the first sheet that gets populated by the webpage is called Wallet2020, or Wallet2021 or whatever the year is.
The second sheet that gets the refresh data is called simply by the year number, i.e. 2020 or 2021, etc.
The problem with the refresh is the Wallet#### sheet when uploading new data changes row numbers per the Month and Day.
Example from Wallet2022 & subsequent sheet 2022 follows:
Cell J4 in sheet 2022 contains formula =Wallet2022!C155. Wallet2022, C155 is blank, but previous to today’s refresh from the web this cell, C155, contained data, but the data rows have changed and now begin with row 159. So as a result of this, in sheet 2022, the data from Wallet2022!C159 (this is the first row in Wallet2022 containing expected the exchange rate data type and is relevant to the first day of January, i.e. Sat-01 Jan 2022) does not appear in sheet 2022 until Wed-05 Jan 2022, four days later. Now every month in 2022 is four days off.
The solution I believe must stem from an if statement, get the date from column “A”, if the date is specific (for example January 1, 2022) then the formula returns a cell row and that cell row number would then be used in the formula on sheet 2022, in the cell relevant to the data seek and the date shown in the far left column in sheet 2022.
Attached images should help to clarify, if not let me know what else I need to supply to clarify.
Hopefully, this is doable because without this then I need to bump up the row number in the formulas in columns J, K, & L in sheet 2022 to match the queried data in sheet Wallet2022, very time-consuming.
I thought about having a helper sheet for each Wallet#### sheet that gets this data and the data then gets input to the year sheet (in this case 2022).
Any help will be much appreciated.
The webpage I get data from is 12 months of exchange data.
So the first sheet that gets populated by the webpage is called Wallet2020, or Wallet2021 or whatever the year is.
The second sheet that gets the refresh data is called simply by the year number, i.e. 2020 or 2021, etc.
The problem with the refresh is the Wallet#### sheet when uploading new data changes row numbers per the Month and Day.
Example from Wallet2022 & subsequent sheet 2022 follows:
Cell J4 in sheet 2022 contains formula =Wallet2022!C155. Wallet2022, C155 is blank, but previous to today’s refresh from the web this cell, C155, contained data, but the data rows have changed and now begin with row 159. So as a result of this, in sheet 2022, the data from Wallet2022!C159 (this is the first row in Wallet2022 containing expected the exchange rate data type and is relevant to the first day of January, i.e. Sat-01 Jan 2022) does not appear in sheet 2022 until Wed-05 Jan 2022, four days later. Now every month in 2022 is four days off.
The solution I believe must stem from an if statement, get the date from column “A”, if the date is specific (for example January 1, 2022) then the formula returns a cell row and that cell row number would then be used in the formula on sheet 2022, in the cell relevant to the data seek and the date shown in the far left column in sheet 2022.
Attached images should help to clarify, if not let me know what else I need to supply to clarify.
Hopefully, this is doable because without this then I need to bump up the row number in the formulas in columns J, K, & L in sheet 2022 to match the queried data in sheet Wallet2022, very time-consuming.
I thought about having a helper sheet for each Wallet#### sheet that gets this data and the data then gets input to the year sheet (in this case 2022).
Any help will be much appreciated.
