MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- Windows
I use this workbook for collecting and maintaining a listing of Fortissimo Exchange Rates per the dates shown. I base my use of Fortissimo on my annual user account with the fiscal year beginning April 1 and ending March 31 of the following year. This you will see in the attached Xl2bb Mini Sheet.
The sheet CZK2USD only shows the dates beginning May 8, which is the date I began using this sheet to collect the data.
You will notice that the dollar amount columns, headed with the name of the month, e.g. April are relevant to the rate columns, labeled with the month and year, e.g. Apr 2023.
You will also notice I have included some minimum & maximum rates, dollar amounts, and associated dates.
You will also notice that the dollar amounts are based on an exchange of 10,000 CZK, which is much different from the file from which this bogus file was created. In addition, all rate & date data is copied from a helper file.
What I have been struggling with is after having established the current month and from that single piece of data, i.e. the current month, then get the minimum & maximum rates and dollar amounts from the column associated with the current month, along with the associated dates of these minimums and maximums.
I have tried several different approaches using the following as search terms and from these searches tried several different formulas but to no avail.
how to get the Current month from a list of months
lookup a value to find in a row range
get a value from a column range that matches a column label
return column location if statement true
Any help in the right direction would be much appreciated.
The sheet CZK2USD only shows the dates beginning May 8, which is the date I began using this sheet to collect the data.
You will notice that the dollar amount columns, headed with the name of the month, e.g. April are relevant to the rate columns, labeled with the month and year, e.g. Apr 2023.
You will also notice I have included some minimum & maximum rates, dollar amounts, and associated dates.
You will also notice that the dollar amounts are based on an exchange of 10,000 CZK, which is much different from the file from which this bogus file was created. In addition, all rate & date data is copied from a helper file.
What I have been struggling with is after having established the current month and from that single piece of data, i.e. the current month, then get the minimum & maximum rates and dollar amounts from the column associated with the current month, along with the associated dates of these minimums and maximums.
I have tried several different approaches using the following as search terms and from these searches tried several different formulas but to no avail.
how to get the Current month from a list of months lookup a value to find in a row range get a value from a column range that matches a column label return column location if statement trueAny help in the right direction would be much appreciated.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1,X1,V1,T1,R1,P1,N1,L1,J1,H1,F1,D1 | B1 | =XLOOKUP(C1,C8:C24,B8:B24,0) |
| C1,Y1,W1,U1,S1,Q1,O1,M1,K1,I1,G1,E1 | C1 | =MIN(C8:C24) |
| B2,X2,V2,T2,R2,P2,N2,L2,J2,H2,F2,D2 | B2 | =XLOOKUP(C2,C8:C24,B8:B24,0) |
| C2,Y2,W2,U2,S2,Q2,O2,M2,K2,I2,G2,E2 | C2 | =MAX(C8:C24) |
| AA1 | AA1 | =EXTRACTNUMBERS(AD2,TRUE) |
| AB1 | AB1 | =SUM(AA1+1) |
| AC1 | AC1 | =EXTRACTNUMBERS(AB1) |
| AC2 | AC2 | =ADDRESS(AC1,1) |
| AD2 | AD2 | =COUNTA(A:A) |
| AG1 | AG1 | =MIN($B$8:$B$24) |
| AG2 | AG2 | =MAX($B$8:$B$24) |
| A3 | A3 | =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))) |
| AD3,AF3 | AD3 | =TODAY() |
| AE3:AE4 | AE3 | =TEXT(AD3,"yyyy") |
| AD4 | AD4 | =EDATE(AD3,12) |
| C4,Y4,W4,U4,S4,Q4,O4,M4,K4,I4,G4,E4 | C4 | =TEXT(C5,"mmm ") |
| C5,Y5,W5,U5,S5,Q5,O5,M5,K5,I5,G5,E5 | C5 | =C6 |
| AJ5 | AJ5 | =TEXT(AF3,"mmm yyyy") |
| AI1 | AI1 | =XLOOKUP($AG$1,$B$8:$B$24,$A$8:$A$24,0) |
| AI2 | AI2 | =XLOOKUP($AG$2,$B$8:$B$24,$A$8:$A$24,0) |
| AI3 | AI3 | =XLOOKUP($C$1,$C$8:$C$24,$A$8:$A$24,0) |
| AI4 | AI4 | =XLOOKUP($C$2,$C$8:$C$24,$A$8:$A$24,0) |
| AI5 | AI5 | =MONTH(AF3) |
| AI6 | AI6 | =XLOOKUP(AJ5,AJ8:AJ19,B7:X7,0) |
| B7,R7,P7,N7,L7,J7,H7,F7,D7 | B7 | =C4&$AE$3 |
| T7,X7,V7 | T7 | =U4&$AE$4 |
| AH7 | AH7 | =ISNUMBER(MATCH(AJ5,B7:X7,0)) |
| AI7 | AI7 | =ADDRESS(MATCH(AJ5,B7:X7,0),3) |
| AH9 | AH9 | =INDEX(B8:Y24,MATCH(AJ5,B7:X7,1),MATCH(AJ5,B8:Y24,1)) |
| AJ9 | AJ9 | =$D$7 |
| AJ10 | AJ10 | =$F$7 |
| AJ11 | AJ11 | =$H$7 |
| AJ12 | AJ12 | =$J$7 |
| AJ13 | AJ13 | =$L$7 |
| AJ14 | AJ14 | =$N$7 |
| AJ15 | AJ15 | =$P$7 |
| AJ16 | AJ16 | =$R$7 |
| AJ17 | AJ17 | =$T$7 |
| AJ18 | AJ18 | =$V$7 |
| AJ19 | AJ19 | =$X$7 |
| Y8:Y24,W8:W24,U8:U24,S8:S24,Q8:Q24,O8:O24,M8:M24,K8:K24,I8:I24,G8:G24,E8:E24,C8:C24 | C8 | =SUM($AF$6/B8) |
