Solved

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

Posted on 2014-01-18
34
269 Views
Last Modified: 2014-01-23
This extends the solution to Part 1.  In this extension, I would like the same solution but to the yellow sections at the top of the forecast tab prior to row 40.  The main difference is that instead of queuing off of the item number, this will queue off of Segment which is in Column I.

Thanks in advance
Forecast.xlsm
UpdatePrevMonthForcast.xlsm
0
Comment
Question by:ScottBarnes
  • 19
  • 15
34 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
The main difference is that instead of queuing off of the item number, this will queue off of Segment which is in Column I.
>>> What does this means ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
What about row 34 it does not have a corresponding item in Col I ?
also
Row 2 and 3 have yellow cells but no corresponding items in Col I how to treat those also ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Try this it should do from row 4 to row 33.
Will wait for your reply on above questions for rows 2,3.34 that have yellow cells but no corresponding Segment in Col I.

Also this solution is not tested !!!! failing to put before and after files prevent me from effective testing. Hope it will work but can't guarantee it.
gowflow
UpdatePrevMonthForcast-V01.xlsm
0
 

Author Comment

by:ScottBarnes
Comment Utility
The newest code for the top section doesn't seem to working and that is probably because I left out an important piece of information that I apologize for up front.  Most of the yellow boxes in the top section are not formulas.  For the top section of the worksheet, the yellow boxes are not formula's except for the WOS Column J.  Row 2,3, and 34 are not formula's either and will not be dependent on Item or Segment. Most of the top section is used to gather general information used in the forecasting process, but I don't want the user to have enter the same information each month.

 Since a lot of the yellow boxes in the top section are not formulas I'm wondering if the best thing to do is copy all yellow boxes over to the new spreadsheet?  Otherwise, you would have to have a way to see if the values changed and that would be difficult.
Forecast-Original.xlsm
Forecast-Updated.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well what do you want ?

Do you want to copy the data only if it is changed ? you have to tell me exactly what you do manually and I will replicate. I cannot simply guess.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I cannot open the last 2 files you posted thru the macro as it seems you are running a version That is higher than what I have. I have Excel 2007.

You need to save the files as .xlsx (Workbook type) not Macro Enabled .xlsm if you have the option to save them as Excel 2007 then it would be great.

Pls answer my post above as do not know what you want me to copy. All items or only different items ? not clear
Regards
0
 

Author Comment

by:ScottBarnes
Comment Utility
I would prefer to only copy the data if something has changed, keeping in mind that the rows that do have segment should be able to know if the segment position on the updated spreadsheet has changed, similar to what you did from row 40 on for Item Number.

I  resaved the files as .xlsx

Thank you,
Forecast-Original.xlsx
Forecast-Updated.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok say in original for 1 cell I have nothing and in the same cell in Updated I hve 25
1) shall I keep 25 or update to nothing.

same if original has 5 and updated has 2 I change to 5 ?
if original has 5 and updated has nothing I update to 5 ?

still not sure how to deal with row
2,3,45 as no segment.

Last
Segment here is not a number is it alphanumeric ? or a number ??
gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
Correct, update using what's in the original unless the updated already has the same value.

For rows 2,3, and 34 if the value in the original is different than the updated you will update to what's in the original, the values in these cells are global to the spreadsheet and not tied to item or segment so you don't have to worry about items or segments changing position.

Segment is Alphanumeric
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well I have a problem the last 2 files you posted Forcast-Original.xlsx and Forcast-Updated.xslx cannot be opened I need you to save this files as version .xls (choose file type Excel 97-2003 Workbook (*.xls)

and post them here.
gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
I posted the file in .xls format

Thank you,
Forecast-Original.xls
Forecast-Updated.xls
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok one more I just noticed on row 2 you only have Cell O2 that is yellow is this correct ? or you forgot to color the other cells of that row like Row 3 ?
gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
Just O2 is correct, the number of order days per week is the same for all months.

Thank you
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok here it is. Please test it thouroully namely:
1) changing files ...
2) trying values in Original at different places ...
3) Presing on the buttons Show This Month file / Last Month File .. Hide etc .. and see behavior.

The most important is when you activate the macro to update you can then press on Show This file and see it and make sure all data is correct.

NEVER, repeat NEVER close the files that the macro has opened ie This and Last file (or as you call them Original and Updated)
BUT instead close the main macro file and if there are changes in the Updated file it will ask you if you want to save the file you can still Not save the file and it will shutdown all opened files.

So the functioning is:
You open the macro file and from within it you choose the files if needed, you activate the macro and from within it you check data and when all is done and finished you close the macro file and in return it will close the original without saving it and will prompt you to save Updated (If it has changed) if not it will close all files.

Let me know your feedback.

LAST pls do NOT Use the older version as this one is more stable. It has a total different logic when it comes to opening workbooks reason why it took me time to reply.
gowflow
UpdatePrevMonthForcastV02.xlsm
0
 

Author Comment

by:ScottBarnes
Comment Utility
I opened the UpdatePrevMonthForcastV02.xlsm above and when I click the first green button to designate the location of the Original file and select the file location I get this error:

Run Time Error 91
-  Object variable or with block variable not set

When I click debug it points to this line of code:
'Update CommandButton4 caption to show Last Month File in use
Thiswb.Sheets("Main").CommandButton4.Caption = "Last Month File: <" & gstLastMoFile & "> ... Activated"

When I receive this message I do not have any other excel files open other than UpdatePrevMonthForcastV02.xlsm

Thank you
0
 

Author Comment

by:ScottBarnes
Comment Utility
I'm not sure what the error message above was, I rebooted my machine for another reason and I decided to try your solution again and I was able to load the files.

I will test thoroughly and let you know.

I thing I did want to ask you is about your note that says, NEVER, repeat NEVER close the files that the macro has opened ie This and Last file (or as you call them Original and Updated)

I'm going to have multiple people using this and to me it would be extremely nice you could do either of these options:

1.  Save and Close the Updated Forecast File manually after the hardcoded fields are brought over.  Then close the spreadsheet which contains the "Main" tab.  Currently you get a nasty error message.

or

2.  Be able to do what you suggest and close the spreadsheet which contains the "Main" tab and have it prompt you if you want to save the Updated Forecast File.

It is really hard to remember that you have to close the "Main" workbook first in order for the tool to exit gracefully.

My other questions is whether the spreadsheet with the "Main" tab and the macro can reside in my forecast template vs. being a standalone file.
0
 

Author Comment

by:ScottBarnes
Comment Utility
I've been testing for about an hour and I've very pleased, here are few very small things I've come across:

-  Column N in the top section for Rows 2 thru 34 is not copying over to the Updated Forecast File
-  When you move an items row position from the Original to the Updated Forecast File, the Global Store Adjustment column loses track of which item it belongs to.  

For example, on the Original Forecast File I put a value of 100 in the Global Store Adjustment columns on row 40.  In the Updated Forecast File I moved the item to row 50.  When I use the macro the hardcoded value stays on row 40.

Thank you for all of your help and I will continue to test while I wait to hear back from you on my last two post.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hi
Delay due to time zone here its morning now !

Ok for Col N my mistake it is fixed in this version.
I added a blue button Save and Exit that your users will click on to close all open workbooks being the macro, the original and the updated. They will be prompts same as normal workbook closure you can choose Yes it will save the Updated if you choose No it will not save it and Cancel will cancel the operation and stay where you are and keep all workbooks opened.

AS far as where to put the macro, In my humble feeling and moreover for the comment you have put as several users will have to work on this file, then it is imperative that this file be a stand alone file like it is designed and not part of a workbook that has your data. In any case the way it is designed if you put it in your workbook that have the data it will not work !!!

I hope I was able to help you as the work put here is tremendous trust me it is over 20 hours work !!! that I usually charge high, but was glad to provide to you.
Rgds/gowflow
UpdatePrevMonthForcastV03.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Oooppps  Just noticed you had an other issue:


For example, on the Original Forecast File I put a value of 100 in the Global Store Adjustment columns on row 40.  In the Updated Forecast File I moved the item to row 50.  When I use the macro the hardcoded value stays on row 40.

WHAT IS THIS ??? Global Store Adjustment columns ?? talk to me in Col A,B C .... I don't see this description !!!!
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any news ?
gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
Global Store Cnt Override is Column T for rows 40 and below.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Right. Does this column has a formulas ???

Don't forget that all rows 40 and above (presume you meant above not below like 41, 42 etc..) are still basis formula and if no formulas then we move the value. So if any in these columns you have something else then I cannot simply guess !!! :)
gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
The reason I ask about Column T below row 40 is because there are 12 other columns below column 40 that are not formulas but they still follow the item number when it changes rows based on my testing ( I believe they are labeled Lift Override).  Column T should also follow the item even though it is not a formula.  Please let me know what you think.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I am lost.

Are we talking 40 + or 40 - ???

Then are we to act like row 4 to 33 ?? like if the value is different from Original to Updated then show it in Updated ???

gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
I was talking about 40+.  I was wanting column T to ask just like you have the Lift Override section (column AG:AR) working.  The Lift Override section does not have formulas.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well my friend !!!!

Here is what I was told and what the code work since the ever beginning !!!!

For rows 40 and OVER
Col B till AR Are all working on Formulas

And
Col BL as it is the only one that far that was pink also working on formulas.


If you have something different then PLEASE sake good order and lets not waste more time on this back and forth do something like:

Col B-Z --> Formulas
Col AA ---> Not used
Col AB-AM ---> Values

.... whatever this is just an example ,.. take it as far as you want I have no problem as long as I have the map and it is clear to me. Cut the blabla and column titles as they mean nothing to me. Just column heading A,B,C etc...

gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
Col B-S -->  Formulas
Col T -->  Values
Col U-AF -->  Formulas
Col AG-AR -->  Values
Col AS-BK  -->  Not Used
Col BL -->  Values
Col BN-CY -->  Not Used

Thank you,
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
your missing BM !!!
gowflow
0
 

Author Comment

by:ScottBarnes
Comment Utility
BM --> Not Used
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok here it is.
PLs check it out thouroully

rgds/gowflow
UpdatePrevMonthForcastV05.xlsm
0
 

Author Comment

by:ScottBarnes
Comment Utility
I’ve done about 3 hours’ worth of testing and the tool is working awesome.  There are just two small tweaks that I’m hoping you are still willing to help me with:


1.       Please change the code to not automatically open up the Last Month’s and This Month’s file when your macro tool opens.  Only open up the files once the user attaches a new location for Last Month and This Month file.  The reason I’m asking for this is because for each new month both files are going to change locations and I don’t want the user to accidently update the wrong file.  

2.      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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
these tweaks need work and will be glad to assist however they should 'each' be a new question as already the threads in here are way too long for anyone to follow.
You will need to accept this question and post a related new question to this one with the first item (and post a link in here so I can follow if you want my help) then when the first questions is done then you proceed with an other etc..
gowflow
0
 

Author Closing Comment

by:ScottBarnes
Comment Utility
Your solution is great, since I had some enhancement above and beyond the original questions I posted other questions for the added requirements:

Part 3 - http://www.experts-exchange.com/Microsoft/Applications/Q_28346214.html
Part 4 - http://www.experts-exchange.com/Microsoft/Applications/Q_28346217.html
Part 5 - http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28346222.html
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Why you did this all at the same ttime ??? I said 1 at a time or else there will be a mess !!!
gowflow
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 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

11 Experts available now in Live!

Get 1:1 Help Now