Two Workbooks

MrDB4Excel

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
348
Office Version
  1. 2013
Platform
  1. 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_Apr20-Mar21'!c"&(COUNTA(C:C)+R38),"Paste in F_Apr20-Mar21")
R38 from the above formula is looking at ='[CurrencyConversionRatesWebQuery.xlsm]F_Apr20-Mar21'!$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_Apr20-Mar21 even if this worksheet F_Apr20-Mar21 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_Apr20-Mar21'!$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_Apr20-Mar21 in CurrencyConversionRatesWebQuery.xlsm
Third XL2BB attachment is of sheet October in FortissimoHistory.xlsx
FortissimoTabulation.xlsx
KLMNOPQRSTUVWX
1Greyed Text Indicates Actual Rate Used & Kč Received$2,000$4,000$8,200$11,100$13,800
2Fortissimo Exchange Based On $2,700April 2020May 2020June 2020July 2020August 2020
3
4MonthActual RateDollarsCurrent RatesAmt TenderedCrowns ReceivedTime of DataDate of Data$2,000$4,000$8,200$11,100$13,800
5April25.026$2,00022.577$2,00050,052.00 Kč18:27:58Wed-07 October 202022.57722.60822.62622.64022.651
6May24.831$4,00022.608$2,00049,662.00 Kč
7June23.558$8,20022.626$4,20098,943.60 Kč22.5770.0310.0180.0140.011
8July23.402$11,10022.640$2,90067,865.80 Kčééééé
9August21.981$13,80022.651$2,70061,157.70 Kč
10September22.121$16,50022.662$2,70061,187.40 Kč$2,000$4,000$8,200$11,100$13,800
11October$19,20022.662$2,70061,187.40 Kč
12November$21,90022.662$2,70061,187.40 Kč
13December$24,60022.662$2,70061,187.40 Kč
14January$27,30022.662$2,70061,187.40 Kč
15February$30,00022.662$2,70061,187.40 Kč
16March$32,70022.662$2,70061,187.40 Kč18:27:58Wed-07 October 202022.57722.60822.62622.64022.651
17
18Average:22.645Fortissimo History10
19Fortissimo History 2021
20USD to CZK Forecast.xlsx
21YearMonthDay
2210/07/20202020107Currency Exchange Rate Finder Per Date
23OctoberFortissimo
2422.662Exchange Rates Dot Org
2522.670Typically Fortissimo Is .008 Higher Than Website RateUSD to CZK Forecast
26USD/CZK (Dollar to Koruna) Currency Rate FX Forecast and Prognosis Data
27CurrencyConversionRatesWebQueryDaily
28FortissimoTabulationCZ
29Paste in FortissimoHistory.xlsx
30October
31YearMonthDay
3210/07/20202020107
3311/07/202011
34November
3522.662
3622.670Typically Fortissimo Is .008 Higher Than Website Rate
37
38Paste in F_Apr20-Mar21325
39All 12 Months
40Paste in Monthly Income History293
ForTab
Cell Formulas
RangeFormula
T1T1=M5
U1U1=M6
V1V1=M7
W1W1=M8
X1X1=M9
T4T4=$M5
U4U4=$M6
V4V4=$M7
W4W4=$M8
X4X4=$M9
N5N5=D2
R5R5=$F$12
S5S5=$G$12
T5T5=$N5
U5U5=$N6
V5V5=$N7
W5W5=$N8
X5X5=$N9
M6:M16M6=SUM(M5+O6)
N6N6=SUM(N5+U$7)
N7N7=SUM(N6+V$7)
N8N8=SUM(N7+W$7)
N9N9=SUM(N8+X$7)
N10N10=SUM(N9+Y$7)
N11N11=SUM(N10+Z$7)
N12N12=SUM(N11+AA$7)
N13N13=SUM(N12+AB$7)
N14N14=SUM(N13+AC$7)
N15N15=SUM(N14+AD$7)
N16N16=SUM(N15+AE$7)
T7T7=N5
T10T10=$M5
U10U10=M6
V10V10=M7
W10W10=M8
X10X10=M9
P5:P8P5=SUM(L5*O5)
P9:P16P9=SUM(N9*O9)
R16:X16R16=R5
N18N18=AVERAGE(N5:N16)
W18W18='F:\Finances\CSOB\[FortissimoHistory.xlsx]October'!$X$1
N22,N32N22=YEAR(TODAY())
O22,O32O22=MONTH(TODAY())
P22,P32P22=DAY(TODAY())
M22,M32M22=DATE(N22,O22,P22)
M23,M34M23=TEXT(DATE(O22,O22,1),"MMMM")
M24,M35M24=VLOOKUP(M23,$K$5:$N$16,4,0)
M25,M36M25=SUM(M24+0.008)
S29S29=HYPERLINK("[FortissimoHistory.xlsx][FortissimoTabulation.xlsx!S30c"&(COUNTA(C:C)+W18),"Paste in FortissimoHistory.xlsx")
S30S30=TEXT(DATE(O32,O32,1),"MMMM")
O33O33=MONTH(M33)
M33M33=EDATE(M32,1)
M38M38=HYPERLINK("[CurrencyConversionRatesWebQuery.xlsm]'F_Apr20-Mar21'!c"&(COUNTA(C:C)+R38),"Paste in F_Apr20-Mar21")
R38R38='F:\Finances\CSOB\[CurrencyConversionRatesWebQuery.xlsm]F_Apr20-Mar21'!$M$1
M40M40=HYPERLINK("[CurrencyConversionRatesWebQuery.xlsm]'Monthly Income History'!a"&(COUNTA(A:A)+R40),"Paste in Monthly Income History")
R40R40='F:\Finances\CSOB\[CurrencyConversionRatesWebQuery.xlsm]Monthly Income History'!$I$1


CurrencyConversionRatesWebQuery.xlsm
LMNO
1324325325C Column Count
2$B$325324
F_Apr20-Mar21
Cell Formulas
RangeFormula
L1L1=EXTRACTNUMBERS(O2,TRUE)
M1M1=SUM(L1+1)
N1N1=EXTRACTNUMBERS(M1)
N2N2=ADDRESS(N1,2)
O2O2=COUNTA(C:C)

1602089623910.png
This image is for showing column "C" on sheet F_Apr20-Mar21 in CurrencyConversionRatesWebQuery.xlsm

FortissimoHistory.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1$2,000$4,000$8,200$11,100$13,800$16,500$19,200$21,900$24,600$27,300$30,000$32,700Expected Coversion $ To Kč91010C Column CountOctober
2Day #$ AmtTime of DataDate of DataApril 2020May 2020June 2020July 2020August 2020September 2020October 2020November 2020December 2020January 2021February 2021March 2021$2,700$B$109
3OctoberïïïïFortissimo Rate When $'s Exchanged For Kč23.1759000October
41$2,70021:46:02Thu-01 October 202022.84322.87222.89022.90422.91522.92722.92722.92722.92722.92722.92722.92722.869100061,870.50 KčThu-01 Oct 20201
52$2,70016:29:27Fri-02 October 202022.78322.81322.83122.84422.85522.86922.86722.86722.86722.86722.86722.86723.113500061,708.50 KčFri-02 Oct 20202
63$2,7007:02:49Sat-03 October 202022.78322.81322.83122.84422.85522.86922.86722.86722.86722.86722.86722.86723.175900061,708.50 KčSat-03 Oct 20203
74$2,7009:45:14Sun-04 October 202022.78322.81322.83122.84422.85522.86922.86722.86722.86722.86722.86722.86723.175900061,708.50 KčSun-04 Oct 20204
85$2,70021:34:47Mon-05 October 202022.86222.89122.91022.92322.93422.94822.94622.94622.94622.94622.94622.94623.009500061,921.80 KčMon-05 Oct 20205
96$2,70019:44:19Tue-06 October 202022.77322.80222.82122.83422.84522.85922.85922.85922.85922.85922.85922.85922.896300061,681.50 KčTue-06 Oct 20206
107$2,70017:56:45Wed-07 October 202022.57722.60822.62622.64022.65122.66222.66222.66222.66222.66222.66222.66223.013700061,157.70 KčWed-07 Oct 20207
118$2,7000.00 KčSat-00 Jan 19008
129$2,7000.00 KčSat-00 Jan 19009
1310$2,7000.00 KčSat-00 Jan 190010
October
Cell Formulas
RangeFormula
E1E1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$5
F1F1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$6
G1G1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$7
H1H1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$8
I1I1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$9
J1J1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$10
K1K1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$11
L1L1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$12
M1M1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$13
N1N1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$14
O1O1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$15
P1P1='F:\Finances\CSOB\[FortissimoTabulation.xlsm]ForTab'!$M$16
W1W1=EXTRACTNUMBERS(Z2,TRUE)
X1X1=SUM(W1+1)
Y1Y1=EXTRACTNUMBERS(X1)
AA1AA1=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Y2Y2=ADDRESS(Y1,2)
Z2Z2=COUNTA(N:N)
C3C3=MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,32)
Q3Q3=MAX(Q4:Q33)
S2S2='F:\Finances\CSOB\[2015_CSOB-CZK.xlsm]Projections'!$H$17
S3S3=C3
R4:R13R4=SUM(I4*$S$2)
S4:S13S4=D4
B4:B13B4=$S$2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K4:K34,R4:R34Expression=AND($C$3="October")textNO


If anything does not make sense please ask what doesn't and I will expound as needed.
 

Attachments

  • 1602089332933.png
    1602089332933.png
    7.2 KB · Views: 22

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You must log in or register to reply here.

Similar threads

E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
93
earth
E
D
  • Solved
New formula for cumulative values
Replies
6
Views
196
djclements
djclements
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
527
XLfriend777
XLfriend777
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel
JenniferMurphy
  • Question Question
Best way to convert Amazon ratings to meaningful Z Scores
Replies
7
Views
722
JenniferMurphy
JenniferMurphy

Forum statistics

Threads
1,224,829
Messages
6,181,219
Members
453,024
Latest member
Wingit77

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