Find Date, Get Row Number Based On Found Date, Apply Row Number To Another Formula

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. 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.
Sheet2022.jpg
Sheet-Wallet2022.jpg
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,352
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top