MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- Windows
Equals Cell Above
So here is the dilemma. My last post involved setting a conditional format to set each row that is a Sunday row to be highlighted a specific color. This works great, such that when I copied the 2018 sheet and renamed it to 2019, adjusted the top 2 column A cells which then changed all date cells to read 2019 (except for the first row which was set to equal A372 of the 2018 sheet (December 31, 2018). Then setting the second date cell (A8) to the formula =A7+1 consequently changing all subsequent rows from 2018 to 2019. When this happened then all Sunday rows shifted obviously, but the highlight color moved with this change so that now for the 2019 sheet all Sunday rows are highlighted as expected.
This same row (each Sunday row also needs to always equal the Saturday row, but only in columns B thru K.
When I transfer exchange rates from a separate worksheet which does the web query into this sheet using the “Paste Values and Number Formatting” on a Saturday then the desired outcome is for Sunday to automatically fill in with the same numbers as Saturday simply because I have painstakingly set each Sunday cell for columns B thru K to equal the Saturday cell above it. So for example the table shown below shows Saturday and Sunday in the 2018 sheet which happens to be rows 13 and 14. Thus the formula in B14 would be set to (=B13).
So back to this dilemma. When I set all Sunday cells to equal the Saturday cell above (in columns A thru K) in the 2018 sheet; then copied the 2018 sheet and renamed it to 2019; then adjusted the date column “A” as described above, the Sunday highlighting moved with the changes, but the equal formula to set the Sunday row, columns A thru K, equal to the Saturday row above then moved on to the Monday row.
The desire is for the equal formula to move with the Sunday row when the dates change year to year.
BTW, the reason Sunday is equal to Saturday is because the weekends always retain the same exact exchange rates all 48 hours of the weekend, never changing again until Monday.
What may be extremely helpful is if I could forward the 2 excel workbooks to someone via email so you can have a first hand look at what these look like and how they operate.
Column "A"___________Column "B"______Column "C"______Column "D"_____Column "E"_____Column "F"______Column "G"______Column "H"_____Column "I"______Column "J"_____Column "K"
Date_________________EUR to USD_____USD to EUR_____CZK to USD_____USD to CZK_____RUB to USD_____USD to RUB______RUB to CZK_____CZK to RUB______EUR to CZK____CZK to EUR
Saturday--06 Jan 2018___1.20302_______0.83124________0.04710________21.22940______0.01756________56.93696________0.37286________2.68199________25.53939______0.03916
Sunday--07 Jan 2018____1.20302_______0.83124________0.04710________21.22940______0.01756________56.93696________0.37286________2.68199________25.53939______0.03916
So here is the dilemma. My last post involved setting a conditional format to set each row that is a Sunday row to be highlighted a specific color. This works great, such that when I copied the 2018 sheet and renamed it to 2019, adjusted the top 2 column A cells which then changed all date cells to read 2019 (except for the first row which was set to equal A372 of the 2018 sheet (December 31, 2018). Then setting the second date cell (A8) to the formula =A7+1 consequently changing all subsequent rows from 2018 to 2019. When this happened then all Sunday rows shifted obviously, but the highlight color moved with this change so that now for the 2019 sheet all Sunday rows are highlighted as expected.
This same row (each Sunday row also needs to always equal the Saturday row, but only in columns B thru K.
When I transfer exchange rates from a separate worksheet which does the web query into this sheet using the “Paste Values and Number Formatting” on a Saturday then the desired outcome is for Sunday to automatically fill in with the same numbers as Saturday simply because I have painstakingly set each Sunday cell for columns B thru K to equal the Saturday cell above it. So for example the table shown below shows Saturday and Sunday in the 2018 sheet which happens to be rows 13 and 14. Thus the formula in B14 would be set to (=B13).
So back to this dilemma. When I set all Sunday cells to equal the Saturday cell above (in columns A thru K) in the 2018 sheet; then copied the 2018 sheet and renamed it to 2019; then adjusted the date column “A” as described above, the Sunday highlighting moved with the changes, but the equal formula to set the Sunday row, columns A thru K, equal to the Saturday row above then moved on to the Monday row.
The desire is for the equal formula to move with the Sunday row when the dates change year to year.
BTW, the reason Sunday is equal to Saturday is because the weekends always retain the same exact exchange rates all 48 hours of the weekend, never changing again until Monday.
What may be extremely helpful is if I could forward the 2 excel workbooks to someone via email so you can have a first hand look at what these look like and how they operate.
Column "A"___________Column "B"______Column "C"______Column "D"_____Column "E"_____Column "F"______Column "G"______Column "H"_____Column "I"______Column "J"_____Column "K"
Date_________________EUR to USD_____USD to EUR_____CZK to USD_____USD to CZK_____RUB to USD_____USD to RUB______RUB to CZK_____CZK to RUB______EUR to CZK____CZK to EUR
Saturday--06 Jan 2018___1.20302_______0.83124________0.04710________21.22940______0.01756________56.93696________0.37286________2.68199________25.53939______0.03916
Sunday--07 Jan 2018____1.20302_______0.83124________0.04710________21.22940______0.01756________56.93696________0.37286________2.68199________25.53939______0.03916
