If Find Blank Cell

MrDB4Excel

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
334
Office Version
  1. 2013
Platform
  1. Windows
The purpose of this non-vba function is to go to the next blank cell in a range of four cells in one row.
The value in Sheet ForTab, cell L17 is used during the conclusion of other formulas.
Sheets DailyRate1Apr2023-31Mar2024 & DailyRate1Apr2022-31Mar2023 are used in the formulas found in Sheet ForTab, cells P10:T11.
When a value is entered into L17 based upon data from the Fortissimo website this value is then searched in Sheets DailyRate1Apr2023-31Mar2024 & DailyRate1Apr2022-31Mar2023. If found then the relevant data appears in cells P10:T11. If not found then the relevant cells show #N/A.
If both rows in the cell range P10:T11 show #N/A then I go to another sheet to gather data from Fortissimo and add this new data to the current year sheet DailyRate1Apr2023-31Mar2024 in the next available set of blank rows that appear after the last entry, in this case in sheet DailyRate1Apr2023-31Mar2024 it would be row 30.
The intent of all of this is to automate the filling in of data in various locations.
In addition, when a new day’s date indicates an existing data point shown in Sheet ForTab, cell L17 that matches the data on the Fortissimo website, then I add this new date to the relevant row in sheet DailyRate1Apr2023-31Mar2024 that matches the value in Sheet ForTab, cell L17.
In this row then are other formulas happening that help keep a record of all dates relevant to the value shown in Sheet ForTab, cell L17.
I suspect this is all confusing but I thought it good to give an explanation in words of what is going on in these two sheets that hopefully will help as you examine the included Xl2bb Mini Sheets.
This morning I searched through many websites looking for possible answers and so far have not found any. I hope someone can help. I would very much appreciate any help.

I forgot to mention that the sheet DailyRate1Apr2023-31Mar2024 in this Xl2bb Mini Sheet only represents a small number of rows of data that exist in the sheet I use for all dates relevant to Fortissimo searches. This post represents data taken from a copy of the real workbook I use. In the real workbook I use, in sheet DailyRate1Apr2023-31Mar2024, if you were to be given an Xl2bb of this sheet it would contain 156 rows. Thus I copied the original sheet and deleted many rows to give a much smaller Xl2bb Mini Sheet.

FortissimoTabulationFindBlankCell.xlsx
CDEFGHIJKLMNOPQRSTUVWXY
1MonthActual RateDollarsCurrent RatesAmt TenderedDollars ExchangedCrowns ReceivedTime of DataDate of Data$100$300$600$1,000$1,500$2,100$2,800$3,600$4,500$5,500$6,600$7,800
21Apr-202322.081$10022.471$100$1,60035,329.60 KčApr-2023May-2023Jun-2023Jul-2023Aug-2023Sep-2023Oct-2023Nov-2023Dec-2023Jan-2024Feb-2024Mar-2024
32May-202321.671$30022.475$200$2,20047,676.20 Kč$100$300$600$1,000$1,500$2,100$2,800$3,600$4,500$5,500$6,600$7,800USD to CZK
43Jun-202321.886$60022.479$300$3,20070,035.20 Kč22.9553000
54Jul-202321.057$1,00022.486$400$2,10044,219.70 Kč11:06:50Sun-24 September 202322.47122.47522.47922.48622.49122.49322.49322.49322.49322.49322.49322.493
65Aug-20230.000$1,50022.491$500$00.00 Kč0.0040.0040.0070.0050.0020.0000.0000.0000.0000.0000.000
76Sep-202322.000$2,10022.493$600$2,20048,400.00 KčDailyRate1Apr2023-31Mar2024 ððð0.0040.0040.0070.0050.0020.0000.0000.0000.0000.0000.000
87Oct-20230.000$2,80022.493$700$00.00 KčDailyRate1Apr2022-31Mar2023 ððð           
98Nov-20230.000$3,60022.493$800$00.00 Kč
109Dec-202322.500$4,50022.493$900$1,50033,750.00 Kč$C$2828Fri-22 September 2023Current Rate Location: Apr23-Mar24
1110Jan-20240.000$5,50022.493$1,000$00.00 KčSep 24, 2023 11:06:50#N/A#N/A#N/A#N/A
1211Feb-20240.000$6,60022.493$1,100$00.00 Kč
1312Mar-20240.000$7,80022.493$1,200$00.00 KčExchCalcs
14Paste in DailyRate1Apr2023-31Mar202430Get K6-X7
15
16YearMonthDay11:06:50Sun-24 September 202322.47122.47522.47922.48622.49122.49322.49322.49322.49322.49322.49322.49322.9553000
179/24/20232023924$100Base ð22.471#4 ðððððPaste in F_Apr23-Mar24183
18SeptemberSeptemberððð22.493
19Octoberððð22.493GoTo Relevant Row Per 1st Date/Time Thru =TEXT(4th Date/Time28
ForTab
Cell Formulas
RangeFormula
M1M1=F2
N1N1=F3
O1O1=F4
P1P1=F5
Q1Q1=F6
R1R1=F7
S1S1=F8
T1T1=F9
U1U1=F10
V1V1=F11
W1W1=F12
X1X1=F13
M2M2=D2
N2N2=D3
O2O2=D4
P2P2=D5
Q2Q2=D6
R2R2=D7
S2S2=D8
T2T2=D9
U2U2=D10
V2V2=D11
W2W2=D12
X2X2=D13
M3M3=$F2
N3N3=$F3
O3O3=$F4
P3P3=$F5
Q3Q3=$F6
R3R3=$F7
S3S3=$F8
T3T3=$F9
U3U3=$F10
V3V3=$F11
W3W3=$F12
X3X3=$F13
D2:D13D2=E30
E2:E13E2='F:\Finances\CSOB\2023\[PensionReceiptHistory2023.xlsx]2023 Pension'!$M5
F2F2=H2
G2G2=$L$17
F3:F13F3=SUM(F2+H3)
G3G3=SUM(G2+N$6)
G4G4=SUM(G3+O$6)
G5G5=SUM(G4+P$6)
G6G6=SUM(G5+Q$6)
G7G7=SUM(G6+R$6)
G8G8=SUM(G7+S$6)
G9G9=SUM(G8+T$6)
G10G10=SUM(G9+U$6)
G11G11=SUM(G10+V$6)
G12G12=SUM(G11+W$6)
G13G13=SUM(G12+X$6)
I2:I13I2='F:\Finances\CSOB\2023\[CSOB-Dollar-Banking2023.xlsx]2023'!$P8
J2:J13J2=SUM(E2*I2)
Y4Y4='F:\Finances\CSOB\2023\[CurrencyConversionRatesWebQuery2023.xlsm]Current Rates'!$J$12
K5K5=NOW()-TODAY()
L5L5=F17
M5M5=$G2
N5N5=$G3
O5O5=$G4
P5P5=$G5
Q5Q5=$G6
R5R5=$G7
S5S5=$G8
T5T5=$G9
U5U5=$G10
V5V5=$G11
W5W5=$G12
X5X5=$G13
N6N6=MAX($N$7:$N$8)
O6O6=MAX($O$7:$O$8)
P6P6=MAX($P$7:$P$8)
Q6Q6=MAX($Q$7:$Q$8)
R6R6=MAX($R$7:$R$8)
S6S6=MAX($S$7:$S$8)
T6T6=MAX($T$7:$T$8)
U6U6=MAX($U$7:$U$8)
V6V6=MAX($V$7:$V$8)
W6W6=MAX($W$7:$W$8)
X6X6=MAX($X$7:$X$8)
N7:X7N7=IFERROR(INDEX('DailyRate1Apr2023-31Mar2024'!$D$4:$N$29,MATCH($L$17,'DailyRate1Apr2023-31Mar2024'!$C$4:$C$29,0)+1,COLUMNS($N$5:N5)),"")
N8:X8N8=IFERROR(INDEX('DailyRate1Apr2022-31Mar2023'!$D$4:$N$555,MATCH($L$17,'DailyRate1Apr2022-31Mar2023'!$C$4:$C$555,0)+1,COLUMNS($N$5:N7)),"")
P10P10='DailyRate1Apr2023-31Mar2024'!$U$3
Q10Q10=MATCH($R$10,'DailyRate1Apr2023-31Mar2024'!B:B,0)
R10R10='DailyRate1Apr2023-31Mar2024'!$S$1
T10T10=HYPERLINK("#"&"'DailyRate1Apr2023-31Mar2024'!B"&$Q$10,"Current Rate Location: Apr23-Mar24")
P11P11='DailyRate1Apr2022-31Mar2023'!$U$3
Q11Q11=MATCH($R$11,'DailyRate1Apr2022-31Mar2023'!B:B,0)
R11R11='DailyRate1Apr2022-31Mar2023'!$S$1
T11T11=HYPERLINK("#"&"'DailyRate1Apr2022-31Mar2023'!B"&$Q$11,"Current Rate Location: Apr22-Mar23")
L11L11=NOW()
Q14Q14=HYPERLINK("[FortissimoTabulationFindBlankCell.xlsx]'DailyRate1Apr2023-31Mar2024'!a"&(COUNTA(#REF!)+T14),"Paste in DailyRate1Apr2023-31Mar2024")
T14T14='DailyRate1Apr2023-31Mar2024'!$V$1
K16:X16K16=K5
Y16Y16=Y4
G17G17=YEAR(TODAY())
H17H17=MONTH(TODAY())
I17I17=DAY(TODAY())
J17J17="$"&H2
I18:I19I18=F28
T17T17=HYPERLINK("[CurrencyConversionRatesWebQuery2023.xlsm]'F_Apr23-Mar24'!c"&(COUNTA(A:A)+V17),"Paste in F_Apr23-Mar24")
V17V17='F:\Finances\CSOB\2023\[CurrencyConversionRatesWebQuery2023.xlsm]F_Apr23-Mar24'!$M$1
F17F17=DATE(G17,H17,I17)
F18F18=TEXT(DATE(H17,H17,1),"MMMM")
L18L18=XLOOKUP(F18,I30:I41,G2:G13)
L19L19=XLOOKUP(F29,I30:I41,G2:G13)
R19R19=HYPERLINK("[FortissimoTabulationFindBlankCell.xlsx]'DailyRate1Apr2023-31Mar2024'!ah"&(COUNTA(#REF!)+W19),"GoTo Relevant Row Per 1st Date/Time Thru =TEXT(4th Date/Time")
W19W19='DailyRate1Apr2023-31Mar2024'!$X$3
Named Ranges
NameRefers ToCells
RateHistoryK6_X7=ForTab!$K$5:$X$6K16


FortissimoTabulationFindBlankCell.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Beginning With April 1, 2023$100$300$600$1,000$1,500$2,100$2,800$3,600$4,500$5,500$6,600$7,800Fri-22 September 2023283030E Col Count=TEXT(1st Date/Time2nd Date/Time3rd Date/Time4th Date/Time=TEXT(1st Date/Time=TEXT(2nd Date/Time=TEXT(3rd Date/Time=TEXT(4th Date/Time
2April 2022May 2022June 2022July 2022August 2022September 2022October 2022November 2022December 2022January 2023February 2023March 2023Min ððð2,096.80 Kč100Extra Dates Per Specific Rate22.47128$B$3028
3Max ððð2,247.10 Kč200$C$28Sep 23, 2023 07:22:12$AA$282835
47:47:58Sat-01 April 202321.35721.36121.36621.37021.37421.37821.37821.37821.37821.37821.37821.3782,135.70 KčMDL1100Apr 02, 2023 06:23:06 Apr 03, 2023 06:44:02 Aug 03, 2023 08:54:401Apr 02, 2023 06:23:06Apr 03, 2023 06:44:02Aug 03, 2023 08:54:40Apr 02, 2023 06:23:06Apr 03, 2023 06:44:02Aug 03, 2023 08:54:40
50.0040.0050.0040.0040.0040.0000.0000.0000.0000.0000.0000 
65:59:11Tue-04 April 202321.42521.43021.43421.43821.44021.44521.44721.44721.44721.44721.44721.4472,142.50 KčMDL1100Feb 06, 2023 08:26:24 May 23, 2023 07:12:422Feb 06, 2023 08:26:24May 23, 2023 07:12:42Feb 06, 2023 08:26:24May 23, 2023 07:12:42
70.0050.0040.0040.0020.0050.0020.0000.0000.0000.0000.0000 
87:49:20Tue-02 May 202320.96820.97220.97620.98020.98420.98820.98820.98820.98820.98820.98820.9882,096.80 KčMDL0100Jul 18, 2023 07:23:41 18Jul 18, 2023 07:23:41Jul 18, 2023 07:23:41
90.0040.0040.0040.0040.0040.0000.0000.0000.0000.0000.0000 
107:23:56Wed-03 May 202320.99721.00121.00521.00921.01321.01821.01821.01821.01821.01821.01821.0182,099.70 KčMDL1100Jul 17, 2023 08:36:39 19Jul 17, 2023 08:36:39Jul 17, 2023 08:36:39
110.0040.0040.0040.0040.0050.0000.0000.0000.0000.0000.0000 
128:12:14Thu-01 June 202321.66921.67421.67821.68221.68721.69121.69121.69121.69121.69121.69121.6912,166.90 KčMDL0100 2Feb 07, 2023 08:03:01Mar 23, 2023 06:47:34Feb 07, 2023 08:03:01Mar 23, 2023 06:47:34
130.0050.0040.0040.0050.0040.0000.0000.0000.0000.0000.0000 
149:14:03Fri-02 June 202321.53321.53721.54121.54621.55021.55221.55421.55421.55421.55421.55421.5542,153.30 KčMDL1100Jan 27, 2023 07:46:56 Jun 03, 2023 08:28:15 Jun 04, 2023 07:51:06 Jun 05, 2023 11:04:453Aug 22, 2023 07:04:34 Aug 23, 2023 07:45:16 Jan 27, 2023 07:46:56Jun 03, 2023 08:28:15Jun 04, 2023 07:51:06Jun 05, 2023 11:04:45Jan 27, 2023 07:46:56Jun 03, 2023 08:28:15Jun 04, 2023 07:51:06Jun 05, 2023 11:04:45
150.0040.0040.0050.0040.0020.0020.0000.0000.0000.0000.0000 Aug 22, 2023 07:04:34Aug 23, 2023 07:45:16Aug 22, 2023 07:04:34Aug 23, 2023 07:45:16
167:18:18Sat-08 July 202321.30921.31321.31721.32221.32621.33021.33021.33021.33021.33021.33021.3302,130.90 Kč010012
170.0040.0040.0050.0040.0040.0000.0000.0000.0000.0000.0000 
187:23:47Tue-11 July 202321.31821.32221.32621.33121.33521.33921.33921.33921.33921.33921.33921.3392,131.80 Kč110013
190.0040.0040.0050.0040.0040.0000.0000.0000.0000.0000.0000 
206:40:15Tue-01 August 202321.23121.23521.23921.24321.24821.25221.25221.25221.25221.25221.25221.2522,123.10 Kč010020
210.0040.0040.0040.0050.0040.0000.0000.0000.0000.0000.0000 
228:14:36Thu-24 August 202321.62021.62421.62921.63321.63721.64221.64221.64221.64221.64221.64221.6422,162.00 KčMDL0100Aug 25, 2023 06:20:41 26Aug 25, 2023 06:20:41Aug 25, 2023 06:20:41
230.0040.0050.0040.0040.0050.0000.0000.0000.0000.0000.0000 
247:17:46Sun-03 September 202321.72821.73221.73621.74121.74521.74921.74921.74921.74921.74921.74921.7492,172.80 KčMDL0100Sep 04, 2023 07:05:22 2Sep 04, 2023 07:05:22Sep 04, 2023 07:05:22
250.0040.0040.0050.0040.0040.0000.0000.0000.0000.0000.0000 
268:49:37Wed-06 September 202322.05522.05922.06422.06822.07222.07722.07722.07722.07722.07722.07722.0772,205.50 KčMDL0100Jan 00, 1900 00:00:00 4Jan 00, 1900 00:00:00
270.0040.0050.0040.0040.0050.0000.0000.0000.0000.0000.0000 
287:50:14Fri-22 September 202322.47122.47522.47922.48622.49122.49322.49322.49322.49322.49322.49322.4932,247.10 KčMDL0100Sep 23, 2023 07:22:12 Sep 24, 2023 07:32:51 14Sep 23, 2023 07:22:12Sep 24, 2023 07:32:51Sep 23, 2023 07:22:12Sep 24, 2023 07:32:51
290.0040.0040.0070.0050.0020.0000.0000.0000.0000.0000.0000 
30
DailyRate1Apr2023-31Mar2024
Cell Formulas
RangeFormula
C1C1=ForTab!$M$1
D1D1=ForTab!$N$1
E1E1=ForTab!$O$1
F1F1=ForTab!$P$1
G1G1=ForTab!$Q$1
H1H1=ForTab!$R$1
I1I1=ForTab!$S$1
J1J1=ForTab!$T$1
K1K1=ForTab!$U$1
L1L1=ForTab!$V$1
M1M1=ForTab!$W$1
N1N1=ForTab!$X$1
S1S1=XLOOKUP($U$2,$C$4:$C$29,$B$4:$B$29,0)
U1U1=EXTRACTNUMBERS(X2,TRUE)
V1V1=SUM(U1+2)
W1W1=EXTRACTNUMBERS(V1)
AE1:AH1AE1=$Y$1&AA1
U2U2=ForTab!L17
V2V2=MATCH(S1,B:B,0)
W2W2=ADDRESS(W1,2)
X2X2=COUNTA(E:E)
P2P2=MIN($O$4:$O$29)
P3P3=MAX($O$4:$O$29)
U3U3=ADDRESS(MATCH($S$1,B:B,0),3)
V3V3=XLOOKUP(U2,$C$4:$C$29,$AA$4:$AA$29,0)
W3W3=ADDRESS(MATCH($V$3,AA:AA,0),27)
X3X3=RIGHT(W3,LEN(W3)-FIND(CHAR(160),SUBSTITUTE(W3,"$",CHAR(160),2)))
Y3Y3=SUM(X3+7)
O4,O28,O26,O24,O22,O20,O18,O16,O14,O12,O10,O8,O6O4=IFERROR(IF(SUM(C4*R4)=0,"",(SUM(C4*R4))),"")
R4:R29R4=IF(YEAR(B4)=2023,$R$2,IF(YEAR(B4)=2024,$R$3,""))
S4,S10,S8S4=AE4&CHAR(10)&AF4&CHAR(10)&AG4
AE28:AF28,AE26,AE24,AE22,AE15:AF15,AE14:AH14,AE12:AF12,AE10,AE8,AE6:AF6,AE4:AG4AE4=TEXT(AA4,"mmm dd, yyyy hh:mm:ss")
Q29,Q27,Q25,Q23,Q21,Q17:Q19,Q13:Q15,Q9:Q11,Q5:Q7Q5=MOD(ROWS(Q$2:Q3),2)
S6S6=AE6&CHAR(10)&AF6
Q8,Q28,Q26,Q24,Q22,Q20,Q16,Q12Q8=MOD(ROWS(Q$2:Q7),2)
S14,S28,S26,S24,S22S14=AE14&CHAR(10)&AF14&CHAR(10)&AG14&CHAR(10)&AH14
U14U14=AE15&CHAR(10)&AF15&CHAR(10)& AG15&CHAR(10)&AH15
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
63
Fluff
Fluff
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
568
juanam
J
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
221
amphead
A
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
318
Fluff
Fluff
E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
414
Fluff
Fluff

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

Share this page

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
Back
Top