Solved

Part 4 - Copy only hardcoded values to the correct row and column of a another spreadsheet

Posted on 2014-01-23
13
466 Views
Last Modified: 2014-01-24
Currently for the cells that have formula’s you are not checking to see if the Updated Forecast file already has that value like you are for the cells that do not have formula’s.  What I’m seeing is some weird counting issues.  For example, let’s say you hardcode 1 record and that record is a cell that has a formula.  After you click the “Update This Months Forecast”  button it will say “A total of 1 cell(s) were updated…”  If you were to click the “Update This Months Forecast”  button again without making any other changes, it will continue to say “A total of 01 cell(s) were updated…” even though that value has already been updated and no changes were made.  I would expect it to say “A total of 0 cell(s) were updated…” if that cell has already been updated.  However, if you update a cell that does not have a formula, you get a different behavior.  In this case, the first time you click the “Update This Months Forecast”  button it will say “A total of 1 cell(s) were updated…”  If you click the “Update This Months Forecast” button again without making any other changes it will say “A total of 0 cell(s) were updated…” which is what I would expect and I hope we can do the same thing for the cells with formulas.
0
Comment
Question by:ScottBarnes
  • 7
  • 6
13 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39803243

After you click the “Update This Months Forecast”  button it will say “A total of 1 cell(s) were updated…”  If you were to click the “Update This Months Forecast”  button again without making any other changes, it will continue to say “A total of 01 cell(s) were updated…” even though that value has already been updated and no changes were made.

YES for sure as we did not save the file yet after the first run of the macro. remember the macro does not save until you press save and exit and it will then prompt you to save the workbook and it is only after that, if you run it again on the same file that it will say 0 !!!!

We cannot save the file without the consent of the user or then we will be messing with the data and possibly saving data the the user don't want to save !!!

gowflow
0
 

Author Comment

by:ScottBarnes
ID: 39803311
I understand we are not saving the file yet, however, counting behavior is different for cells that are formula's vs. values.

If you click the update button after putting a value in a formula cell it will always say 1 change was made no matter how many times you click the update button.

You get a different behavior on a non-formula cell:

If you click the update button after putting a value in a non-formula cell it will say 1 update the first time you click it and 0 each additional time you click it.

In both cases, no values have been saved but I like the way it works on the non-formula cells.  

Solution:  For formula cells, if the user hardcoded a value, make sure the updated file doesn't already have the hardcoded value, if it already has the hardcoded value becuase you clicked the update button, there is no need to copy it over again.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803337
PLEASE Do me a favor !!!!

Let s not run a bazar here. PLEASE sake good order post 1 question at a time. and reply to the first one first then the rest.

I will wait till we finish Part3 then when close will move to the next.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803529

Solution:  For formula cells, if the user hardcoded a value, make sure the updated file doesn't already have the hardcoded value, if it already has the hardcoded value becuase you clicked the update button, there is no need to copy it over again.

Are you sure this is the solution ???

Shouldn't it be that instaed ??

Solution:  For formula cells, if the user hardcoded a value, then only update the value if the hardcoded value is different from the value in the Updated file (same behavior as values). As your logic maybe correct time being but you may possibly run into situations where users have updated part of the file then saved the workbook and then the next day update the other part !!!

What do you think ?
gowflow
0
 

Author Comment

by:ScottBarnes
ID: 39803583
Yes, I really need this:

Solution:  For formula cells, if the user hardcoded a value, make sure the updated file doesn't already have the hardcoded value, if it already has the hardcoded value becuase you clicked the update button, there is no need to copy it over again.

For cells with fomula's, the reason I only copy it if it is hardcoded is because when the template refreshes the next month the numbers in the formula cells will be different for almost every cell.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803620
ok then here it is.

Check it well and let me know.
gowflow
UpdatePrevMonthForcastV08.xlsm
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:ScottBarnes
ID: 39803716
Very close, here is the behavior I'm looking for when I test:

1.  Works Perfect - I hardcode 1,000 for a formula cell.  I click the update macro button and it updates the file to 1,000 and brings up the dialog box “A total of 1 cell(s) were updated…”  Perfect

2.  Works Perfect - I click the update button again without making any changes and it says “A total of 0 cell(s) were updated…”  Perfect because the update file already has 1,000 in there so no need to update it again.

3.  Doesn't have the behavior I want - If I change the updated file to 1,0001 and click the update macro it should say “A total of 1 cell(s) were updated…” because now the hardcoded value 1,000 is in the original file is different than the value of 1,001 in the update file so it will need to change the value to 1,000.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803804
ok check this one.
gowflow
UpdatePrevMonthForcastV09.xlsm
0
 

Author Comment

by:ScottBarnes
ID: 39804381
The new code works great unless there is an item that move rows, then the following is occurring.

Col H thru S:  Works perfect even if the items moves rows

Col T:  Does not work correctly if an item moves rows.  If you enter a value in this cell and click the update Macro button, it will say “A total of 2 cell(s) were updated…” each time you click it.

Col U thru AF:  Works perfect even if the items moves rows

Col AG thru AR:  Does not work correctly if an item moves rows.  If you enter a value in one of these cells and click the update Macro button, it will say “A total of 2 cell(s) were updated…” each time you click it.

Col BL:  Does not work correctly if an item moves rows.  If you enter a value in this cell and click the update Macro button, it will say “A total of 2 cell(s) were updated…” each time you click it.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39804668
Not sure what will do it but test this version ONLY ON COL T and let me know. If I find out the correct setting then will apply for the rest.
gowflow
UpdatePrevMonthForcastV09.xlsm
0
 

Author Comment

by:ScottBarnes
ID: 39804773
Yes, that fixed Col T even when the row that the item was on changed.  Can you replicate for:

Col AG thru AR

and

Col BL
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39805579
ok here it is
gowflow
UpdatePrevMonthForcastV09.xlsm
0
 

Author Closing Comment

by:ScottBarnes
ID: 39806899
Works Perfect.  Thank you for all of your help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now