Evening all hope someone can help!
Very rusty on excel these days.
Please see the attached spreadsheet:
When you see the sheet first sheet is irrelevant as new tax year but there after i would like:
P9 =
Look up value of P9 in previous sheet and add the value of L9 in active/current sheet.
Q9 =
Look up value of Q9 in previous sheet and add the value of M9 in active/current sheet.
Hope you can help and thanks in advance.
I tried to take at look at this for you but when I clicked the button to create the new sheet, I kept getting errors no matter what I tried or modified in the code. It has to do with naming the sheet, the sheet name (I think) is supposed to be the InputBox value...but the name is always blank with the next number in (). After it names the sheet, the code errors....for me anyway.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
Hi thanks for trying to take a look. I have just tried it on my laptop and seems to be working fine? The date has to be put in with hyphens not forward slashes? Do you have any idea about copying the values from previous s
Hi mezza89
Are you sure it is P9 & Q9 and not P11 & Q11 as the latter has no values
Unless your formulas are as folllows:
O11 =Formula:
Please Login or Register to view this content.
P11 =Formula:Please Login or Register to view this content.
Q11 =Formula:Please Login or Register to view this content.
Or perhaps P9 = P11, Q9 = Q11, O9 = O11 ?
Edited: Code to be fixed
Last edited by Sintek; 04-16-2017 at 03:07 PM. Reason: Added Code
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
And also, should the information in the week, once the total has ben stored not be cleared prior to the new sheet en created.
I think i added a non updated spreadsheet does this one make more sense?
Monday to Sunday columns should be cleared on the new sheet just the yearly totals carried over.
Ahhh HA! Now it makes sense. I think we would have to put a formula in the code to get what you want here. It will take me a while to figure it out because I'm kind of rusty as to how to put formulas in code. Maybe sintek knows how to or has a better way of doing it that wont take as long.
Well any help is hugely appreciated. I use to be very into the programming side but seem to have lost any skill of it over the last few years.
Yes, that makes more sense, Here you go...Try this
Please Login or Register to view this content.
Last edited by Sintek; 04-16-2017 at 04:05 PM.
sintek, I could be wrong but I think op would like to have P9 not only bring the value from L9 on the previous sheet, but also add the value of L9 on the current sheet to it...to keep a running total of the hours. Same for Q9. That's the part I'm having trouble with figuring out.
@gmr4evr1
That's correct....Not sure if I understand, but herewith my understanding
Values of current sheet will be stored....When new sheet is created, he will populate with new hours etc etc.
Then when he creates a another new week, the values of previous sheet plus the val of stored should be tallied...or am I misunderstanding?
How would one put a formula into P9 that adds L9 to The value of twhBal...can't figure that outOriginally Posted by gmr4evr1
Formula:Please Login or Register to view this content.
Exactly where I am stuck. I see how you've worked it out when it adds the new sheet. I was thinking maybe a worksheet selection change event to add L9 to P9 on the current sheet, but I haven't had the chance to try it yet.
I've just realized that with my current code, the only way for the OP to get the tally would be to create the new sheet, so........... The solution is the situation we are trying to figure out.
Something like this:Formula:Please Login or Register to view this content.
Edited formula
Last edited by Sintek; 04-16-2017 at 05:20 PM. Reason: Edited formula
You're on the right track, but the formula is referencing cell AB18 instead of summing P9 & L9. Below is the formula that is placed in P9
Formula:Please Login or Register to view this content.
Maybe I put the code you posted in the wrong place?
mezza89
Is this how you wanted?
Please Login or Register to view this content.
@ jindon
Flawless code as always
Last edited by Sintek; 04-17-2017 at 02:13 AM.
@ gmr4evr1...Of course, herewith amended.You're on the right track, but the formula is referencing cell AB18 instead of summing P9 & L9. Below is the formula that is placed in P9
Formula:Please Login or Register to view this content.
Maybe I put the code you posted in the wrong place?
Please Login or Register to view this content.
Last edited by Sintek; 04-17-2017 at 02:15 AM.
This code seems to work perfectly for what I need. Thank you so much. What does the Clear Balances button do on Sheet 2?
It was just to clear the values of the balances when I was testing code.
Thanks so much for your help!
Pleasure, thanks for the rep point
Yay! Glad this one has been resolved. Thanks for the help sintek and jindon!
@ gmr4evr1
No thank you...Could not have done it without your feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks