MrDB4Excel
Board Regular
 Joined
 Jan 29, 2004
 Messages
 165
 Office Version

 2013
 Platform

 Windows
I have two workbooks I want to work together to jump from one to the other workbook’s next available empty cell in a column.
The following formula works for one specific instance: =HYPERLINK("[CurrencyConversionRatesWebQuery.xlsm]'F_Apr20Mar21'!c"&(COUNTA(C:C)+R38),"Paste in F_Apr20Mar21")
R38 from the above formula is looking at ='[CurrencyConversionRatesWebQuery.xlsm]F_Apr20Mar21'!$M$1
Both these formulas exist in workbook FortissimoTabulation.xlsx
The primary goal using these two together is to jump to the next available empty cell in F_Apr20Mar21 even if this worksheet F_Apr20Mar21 was left with the active cell anywhere else except Column C.
Now the following formula wants to jump to workbook FortissimoHistory.xlsx which has 12 sheets: April, May, June, July, August, September, October, November, December, January, February, and March. =HYPERLINK("[FortissimoHistory.xlsx][FortissimoTabulation.xlsx!S30c"&(COUNTA(C:C)+W18),"Paste in FortissimoHistory.xlsx")
W18 in this formula does the same exact thing as M1 in the formula: ='[CurrencyConversionRatesWebQuery.xlsm]F_Apr20Mar21'!$M$1
My goal is to not update the formula every month but to use the formula =TEXT(DATE(O32,O32,1),"MMMM") in cell S30 of FortissimoTabulation.xlsx which looks to cell M32 that contains the formula: =DATE(N32,O32,P32) which will all be seen in the attached.
This all may seem to not make sense, but after you view the attached it should all come together, I hope.
Many thanks for any help offered.
First XL2BB attachment is of “ForTab”, the only sheet in FortissimoTabulation.xlsx
Second XL2BB attachment is of sheet F_Apr20Mar21 in CurrencyConversionRatesWebQuery.xlsm
Third XL2BB attachment is of sheet October in FortissimoHistory.xlsx
This image is for showing column "C" on sheet F_Apr20Mar21 in CurrencyConversionRatesWebQuery.xlsm
If anything does not make sense please ask what doesn't and I will expound as needed.
The following formula works for one specific instance: =HYPERLINK("[CurrencyConversionRatesWebQuery.xlsm]'F_Apr20Mar21'!c"&(COUNTA(C:C)+R38),"Paste in F_Apr20Mar21")
R38 from the above formula is looking at ='[CurrencyConversionRatesWebQuery.xlsm]F_Apr20Mar21'!$M$1
Both these formulas exist in workbook FortissimoTabulation.xlsx
The primary goal using these two together is to jump to the next available empty cell in F_Apr20Mar21 even if this worksheet F_Apr20Mar21 was left with the active cell anywhere else except Column C.
Now the following formula wants to jump to workbook FortissimoHistory.xlsx which has 12 sheets: April, May, June, July, August, September, October, November, December, January, February, and March. =HYPERLINK("[FortissimoHistory.xlsx][FortissimoTabulation.xlsx!S30c"&(COUNTA(C:C)+W18),"Paste in FortissimoHistory.xlsx")
W18 in this formula does the same exact thing as M1 in the formula: ='[CurrencyConversionRatesWebQuery.xlsm]F_Apr20Mar21'!$M$1
My goal is to not update the formula every month but to use the formula =TEXT(DATE(O32,O32,1),"MMMM") in cell S30 of FortissimoTabulation.xlsx which looks to cell M32 that contains the formula: =DATE(N32,O32,P32) which will all be seen in the attached.
This all may seem to not make sense, but after you view the attached it should all come together, I hope.
Many thanks for any help offered.
First XL2BB attachment is of “ForTab”, the only sheet in FortissimoTabulation.xlsx
Second XL2BB attachment is of sheet F_Apr20Mar21 in CurrencyConversionRatesWebQuery.xlsm
Third XL2BB attachment is of sheet October in FortissimoHistory.xlsx
Cell Formulas  

Range  Formula  
T1  T1  =M5 
U1  U1  =M6 
V1  V1  =M7 
W1  W1  =M8 
X1  X1  =M9 
T4  T4  =$M5 
U4  U4  =$M6 
V4  V4  =$M7 
W4  W4  =$M8 
X4  X4  =$M9 
N5  N5  =D2 
R5  R5  =$F$12 
S5  S5  =$G$12 
T5  T5  =$N5 
U5  U5  =$N6 
V5  V5  =$N7 
W5  W5  =$N8 
X5  X5  =$N9 
M6:M16  M6  =SUM(M5+O6) 
N6  N6  =SUM(N5+U$7) 
N7  N7  =SUM(N6+V$7) 
N8  N8  =SUM(N7+W$7) 
N9  N9  =SUM(N8+X$7) 
N10  N10  =SUM(N9+Y$7) 
N11  N11  =SUM(N10+Z$7) 
N12  N12  =SUM(N11+AA$7) 
N13  N13  =SUM(N12+AB$7) 
N14  N14  =SUM(N13+AC$7) 
N15  N15  =SUM(N14+AD$7) 
N16  N16  =SUM(N15+AE$7) 
T7  T7  =N5 
T10  T10  =$M5 
U10  U10  =M6 
V10  V10  =M7 
W10  W10  =M8 
X10  X10  =M9 
P5:P8  P5  =SUM(L5*O5) 
P9:P16  P9  =SUM(N9*O9) 
R16:X16  R16  =R5 
N18  N18  =AVERAGE(N5:N16) 
W18  W18  ='F:\Finances\CSOB\[FortissimoHistory.xlsx]October'!$X$1 
N22,N32  N22  =YEAR(TODAY()) 
O22,O32  O22  =MONTH(TODAY()) 
P22,P32  P22  =DAY(TODAY()) 
M22,M32  M22  =DATE(N22,O22,P22) 
M23,M34  M23  =TEXT(DATE(O22,O22,1),"MMMM") 
M24,M35  M24  =VLOOKUP(M23,$K$5:$N$16,4,0) 
M25,M36  M25  =SUM(M24+0.008) 
S29  S29  =HYPERLINK("[FortissimoHistory.xlsx][FortissimoTabulation.xlsx!S30c"&(COUNTA(C:C)+W18),"Paste in FortissimoHistory.xlsx") 
S30  S30  =TEXT(DATE(O32,O32,1),"MMMM") 
O33  O33  =MONTH(M33) 
M33  M33  =EDATE(M32,1) 
M38  M38  =HYPERLINK("[CurrencyConversionRatesWebQuery.xlsm]'F_Apr20Mar21'!c"&(COUNTA(C:C)+R38),"Paste in F_Apr20Mar21") 
R38  R38  ='F:\Finances\CSOB\[CurrencyConversionRatesWebQuery.xlsm]F_Apr20Mar21'!$M$1 
M40  M40  =HYPERLINK("[CurrencyConversionRatesWebQuery.xlsm]'Monthly Income History'!a"&(COUNTA(A:A)+R40),"Paste in Monthly Income History") 
R40  R40  ='F:\Finances\CSOB\[CurrencyConversionRatesWebQuery.xlsm]Monthly Income History'!$I$1 
Cell Formulas  

Range  Formula  
L1  L1  =EXTRACTNUMBERS(O2,TRUE) 
M1  M1  =SUM(L1+1) 
N1  N1  =EXTRACTNUMBERS(M1) 
N2  N2  =ADDRESS(N1,2) 
O2  O2  =COUNTA(C:C) 
FortissimoHistory.xlsx  

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z  AA  
1  $2,000  $4,000  $8,200  $11,100  $13,800  $16,500  $19,200  $21,900  $24,600  $27,300  $30,000  $32,700  Expected Coversion $ To Kč  9  10  10  C Column Count  October  
2  Day #  $ Amt  Time of Data  Date of Data  April 2020  May 2020  June 2020  July 2020  August 2020  September 2020  October 2020  November 2020  December 2020  January 2021  February 2021  March 2021  $2,700  $B$10  9  
3  October  ïïïï  Fortissimo Rate When $'s Exchanged For Kč  23.1759000  October  
4  1  $2,700  21:46:02  Thu01 October 2020  22.843  22.872  22.890  22.904  22.915  22.927  22.927  22.927  22.927  22.927  22.927  22.927  22.8691000  61,870.50 Kč  Thu01 Oct 2020  1  
5  2  $2,700  16:29:27  Fri02 October 2020  22.783  22.813  22.831  22.844  22.855  22.869  22.867  22.867  22.867  22.867  22.867  22.867  23.1135000  61,708.50 Kč  Fri02 Oct 2020  2  
6  3  $2,700  7:02:49  Sat03 October 2020  22.783  22.813  22.831  22.844  22.855  22.869  22.867  22.867  22.867  22.867  22.867  22.867  23.1759000  61,708.50 Kč  Sat03 Oct 2020  3  
7  4  $2,700  9:45:14  Sun04 October 2020  22.783  22.813  22.831  22.844  22.855  22.869  22.867  22.867  22.867  22.867  22.867  22.867  23.1759000  61,708.50 Kč  Sun04 Oct 2020  4  
8  5  $2,700  21:34:47  Mon05 October 2020  22.862  22.891  22.910  22.923  22.934  22.948  22.946  22.946  22.946  22.946  22.946  22.946  23.0095000  61,921.80 Kč  Mon05 Oct 2020  5  
9  6  $2,700  19:44:19  Tue06 October 2020  22.773  22.802  22.821  22.834  22.845  22.859  22.859  22.859  22.859  22.859  22.859  22.859  22.8963000  61,681.50 Kč  Tue06 Oct 2020  6  
10  7  $2,700  17:56:45  Wed07 October 2020  22.577  22.608  22.626  22.640  22.651  22.662  22.662  22.662  22.662  22.662  22.662  22.662  23.0137000  61,157.70 Kč  Wed07 Oct 2020  7  
11  8  $2,700  0.00 Kč  Sat00 Jan 1900  8  
12  9  $2,700  0.00 Kč  Sat00 Jan 1900  9  
13  10  $2,700  0.00 Kč  Sat00 Jan 1900  10  
October 
Cell Formulas  

Range  Formula  
E1  E1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$5 
F1  F1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$6 
G1  G1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$7 
H1  H1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$8 
I1  I1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$9 
J1  J1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$10 
K1  K1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$11 
L1  L1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$12 
M1  M1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$13 
N1  N1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$14 
O1  O1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$15 
P1  P1  ='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$16 
W1  W1  =EXTRACTNUMBERS(Z2,TRUE) 
X1  X1  =SUM(W1+1) 
Y1  Y1  =EXTRACTNUMBERS(X1) 
AA1  AA1  =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) 
Y2  Y2  =ADDRESS(Y1,2) 
Z2  Z2  =COUNTA(N:N) 
C3  C3  =MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,32) 
Q3  Q3  =MAX(Q4:Q33) 
S2  S2  ='F:\Finances\CSOB\[2015_CSOBCZK.xlsm]Projections'!$H$17 
S3  S3  =C3 
R4:R13  R4  =SUM(I4*$S$2) 
S4:S13  S4  =D4 
B4:B13  B4  =$S$2 
Cells with Conditional Formatting  

Cell  Condition  Cell Format  Stop If True  
K4:K34,R4:R34  Expression  =AND($C$3="October")  text  NO 
If anything does not make sense please ask what doesn't and I will expound as needed.