John Caines
Well-known Member
- Joined
- Aug 28, 2006
- Messages
- 1,155
- Office Version
- 2019
- Platform
- Windows
Hello all.
I've been working hard on a spreadsheet,, and need just 1 macro to finish it off, but I know
I can't write it myself,,, So I really could do with some help if possible.
I'll try and provide as much info as possible and a test sheet.
OK,,, here goes
I have a worksheet called "Data Import". This needs to export some data to another sheet called "Daily Targets" The daily targets sheet cells that will be needed to import this data are Cells J23 (For a starting £ amount) & Cells; Range J24:J274, as daily amounts.
(AS A NOTE: there are 240days of data to be imported In the Range,, every 20th cell, is blank,, due to how I formatted the sheet,,, ,,
So No data for cells J44,J65,J86,J107,J128,J149,J170,J191,J212,J233,J254
In the Daily import sheet, there are really 3 main steps.
Step 1. Cell F27 is a starting £ amount,, for this example £10,000,,
this needs to be exported to the "Daily Targets" sheet, & inserted into cell J23.
Now Step 2
=========
There are 4 parts to Step 2,, 2a,2b,2c, & 2d.
ONLY 1 of these needs to be filled in.
If more than 1 is filled in the macro needs a fail safe to say, "hey, you've made a mistake,,
only enter 1 part; a,or b or c or d for this step 2!"
Step 2a
======
Is Cell K15. This is a % cell, and someone would enter a daily % increase,, for example 0.5% This means,, everyday,, the Starting amount (Now in Cell J23,, IE £10,000 would increase by .05% every day,, compounding for 240 days
(I'm not sure how to write this 1
Example, if Cell K15 was 0.5%,, then the 1st cell in the "Daily Targets" sheet,
cell J24 would need to show £10,050
Step 2b
======
Is Cell K20. This is a Fixed £ gain amount. Example, If someone entered £100, this would mean that everyday, you would look for £100 gain.
So,, I think this formula is really no more than,J24:J274 must equal K20
(But the 11 other J cells would be blank as mentioned earlier),,,
Example,, If K20 was £100,, The 1st cell in the "daily Targets" sheet , cell J24 would now show £10,100, the 2nd cell J25 would be £10,200
Step 2c
======
Is Cell K25. This is a desired Year End amount. So if someone entered £50,000,, the formula would probably be no more than K25/ 240,, so all the J Cells have a even distribution of the £50,000.
Example; with a £50,000 amount inserted into K25, this gives a even £208.33 daily gain,, so Cell J24 (Day 1 in the Daily Target" sheet) would now show £10,208.33, & Day 2, Cell J25 would show £10,416.66 etc etc
Step 2d
======
Is Cell Range K36:K275,, this is a custom import,, IE you can manually enter 240 days of data.
So, if on Day 1 (Cell K36 of sheet Data Import) you wanted to gain £100, you would enter £100. This would mean the amount exported to Cell J24 in the daily targets sheet would now show £10,100 If Day 2 Cell K37 was £300,, Cell J25 of the "Daily Target" sheet would show a amount of £10,400
(All 240 cells would need data in for this to import,, so again, maybe a fail safe if there wasn't 240 entries
I hope all the above makes sense.
It's just a way for someone to import a starting equity £ (IE £10,000)
& ,,,,, several ways to insert Daily Targeted gains into the Range J24:J274.
I hope someone can help me with this please.
here is a link for a spreadsheet with it all configured.
http://dl.dropbox.com/u/16052166/MrExcel-Target-Sheet.zip
It's the last piece of the puzzle,, but my VBA isn't up to this
It is a bit complicated, hence why I'm uploading a formatted sheet also
Many thanks for all your time with this
Best regards
John Caines
I've been working hard on a spreadsheet,, and need just 1 macro to finish it off, but I know
I can't write it myself,,, So I really could do with some help if possible.
I'll try and provide as much info as possible and a test sheet.
OK,,, here goes
I have a worksheet called "Data Import". This needs to export some data to another sheet called "Daily Targets" The daily targets sheet cells that will be needed to import this data are Cells J23 (For a starting £ amount) & Cells; Range J24:J274, as daily amounts.
(AS A NOTE: there are 240days of data to be imported In the Range,, every 20th cell, is blank,, due to how I formatted the sheet,,, ,,
So No data for cells J44,J65,J86,J107,J128,J149,J170,J191,J212,J233,J254
In the Daily import sheet, there are really 3 main steps.
Step 1. Cell F27 is a starting £ amount,, for this example £10,000,,
this needs to be exported to the "Daily Targets" sheet, & inserted into cell J23.
Now Step 2
=========
There are 4 parts to Step 2,, 2a,2b,2c, & 2d.
ONLY 1 of these needs to be filled in.
If more than 1 is filled in the macro needs a fail safe to say, "hey, you've made a mistake,,
only enter 1 part; a,or b or c or d for this step 2!"
Step 2a
======
Is Cell K15. This is a % cell, and someone would enter a daily % increase,, for example 0.5% This means,, everyday,, the Starting amount (Now in Cell J23,, IE £10,000 would increase by .05% every day,, compounding for 240 days
(I'm not sure how to write this 1
Example, if Cell K15 was 0.5%,, then the 1st cell in the "Daily Targets" sheet,
cell J24 would need to show £10,050
Step 2b
======
Is Cell K20. This is a Fixed £ gain amount. Example, If someone entered £100, this would mean that everyday, you would look for £100 gain.
So,, I think this formula is really no more than,J24:J274 must equal K20
(But the 11 other J cells would be blank as mentioned earlier),,,
Example,, If K20 was £100,, The 1st cell in the "daily Targets" sheet , cell J24 would now show £10,100, the 2nd cell J25 would be £10,200
Step 2c
======
Is Cell K25. This is a desired Year End amount. So if someone entered £50,000,, the formula would probably be no more than K25/ 240,, so all the J Cells have a even distribution of the £50,000.
Example; with a £50,000 amount inserted into K25, this gives a even £208.33 daily gain,, so Cell J24 (Day 1 in the Daily Target" sheet) would now show £10,208.33, & Day 2, Cell J25 would show £10,416.66 etc etc
Step 2d
======
Is Cell Range K36:K275,, this is a custom import,, IE you can manually enter 240 days of data.
So, if on Day 1 (Cell K36 of sheet Data Import) you wanted to gain £100, you would enter £100. This would mean the amount exported to Cell J24 in the daily targets sheet would now show £10,100 If Day 2 Cell K37 was £300,, Cell J25 of the "Daily Target" sheet would show a amount of £10,400
(All 240 cells would need data in for this to import,, so again, maybe a fail safe if there wasn't 240 entries
I hope all the above makes sense.
It's just a way for someone to import a starting equity £ (IE £10,000)
& ,,,,, several ways to insert Daily Targeted gains into the Range J24:J274.
I hope someone can help me with this please.
here is a link for a spreadsheet with it all configured.
http://dl.dropbox.com/u/16052166/MrExcel-Target-Sheet.zip
It's the last piece of the puzzle,, but my VBA isn't up to this
It is a bit complicated, hence why I'm uploading a formatted sheet also
Many thanks for all your time with this
Best regards
John Caines