Home |
Search |
Today's Posts |
#81
Posted to microsoft.public.excel.programming
|
|||
|
|||
leading zeros using ActiveCell.Offset().value to insert row and value
On Fri, 1 Mar 2013 02:58:53 +0000, TimLeonard wrote:
HOW can the program know that Node 2 is being used if there are no loops selected? THere are a few ways nodes can be present with no loops for example --A Voice panel, this one gets programmed using only zones... --A Remote Annunciator is a node that is only to display what the panel displays, but it would be located at the main entrance of a building for the fire department to see the status of the main panel as they enter the building. --A computer workstation that is used to show the status of the system on a computer with graphics is a node --And there are cards that can page or email when a device is in alarm or trouble that is considered a node...All of these have no loops. As far as how the system knows...It just complies all the node information down the list on the PanelData spreadsheet. I know on the voice panel it just shows the node number and uses zeros for the loop and has the zones Z000-Z999. I will need to double check how the others that I mentioned are shown and if they capture the zones....I will post this later as I will have to download it to find the answer Did you solve the problem of the ambiguous name? Yes i did, there was two macros with the same name +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ I have not finished with the work to allow the engineer to add nodes or loops in the field yet, but I have a routine to check and return the differences between the CompareData and Summary sheets. I have made some assumptions with I need to check with you. I am considering differences to be either Changes Deletions Additions The comparison is run on the SUBSET of rows (on the Summary and CompareData sheets) that have an entry in the DeviceTypes column. On Summary, if the engineer deletes the data in all of the columns we allow him to edit, the DeviceTypes entry will blank. Since CompareData is derived from PanelData, and PanelData is downloaded from the field, I am assuming that PanelData will not include a DeviceType entry of 1 or 2 or 3 unless there is a Device present. If that assumption is true, then: 1. Any row on the Summary sheet that has data in the first (ProjectNumber) column is "Changed" 2. Any row on the Summary sheet, where the data in the columns for DeviceLabel ExtendedLabel and/or TypeCodeLabel do not agree with the data in those columns for the row on the CompareData sheet that has the same Merged Address is "Changed" 3. Any row on the Summary sheet which contains a MergedAddress entry that does not exist on CompareData is an "Addition" 4. Any row on the CompareData sheet for which the MergedAddress does not exist on the Summary sheet is a "Deletion" So I wind up with a list of MergedAddresses that can fall into one of the above three categories (and can be used to lookup the rest of the row). How exactly would you want this information presented? One way would be to have three tables Added -- and give the row information from the Summary sheet Deleted -- and give the row information from the CompareData sheet Changed -- and I suppose for this we could output the data from both sheets If we present the row information, do you want to present ALL of the columns? Or just a subset of the columns? One further question: On PanelData, I have noted that unpopulated Extended Label fields are, in fact, populated with thirteen (13) spaces. Do you have any objection if, in creating the CompareData and Summary sheets, we remove these spaces? Also, where are you located. I'm in Maine, in the US, and your responses usually get here in the evening local time. -- Ron |
#82
|
||||
|
||||
Quote:
Quote:
Comments for items 1-4 above: 1. I think the (Project Number) column from the summary sheet should be considered as "informational"...No update option required...since it will only be on the summary sheet until it is programmed in the field....so instead of 3 items/tables it may be 4 2. the thought of "changed" fields...If possible this should have the option to update the summary sheet with the changes or differences found... 3. A row on the Summary sheet that does not exist on CompareData is an "Addition"...Again this is informational since we are not concerned with updating the comparedata sheet...No update option required... 4. This one is a little tricky or misleading. In one case the PanelData/CompareData sheet could have a device not on the summary sheet due to having to add additional devices in the field, which will eventually be populated on the summary sheet but not until the as-built of the drawings and update of the summary sheet. In another case the field technician could delete more devices and the engineer expected and thus the summary sheet would not reflect this. In this case it truly would be a deletion that would need to be reflected on the summary sheet... So it should have the option to update the summary sheet with the differences found if possible Quote:
Quote:
|
#83
Posted to microsoft.public.excel.programming
|
|||
|
|||
leading zeros using ActiveCell.Offset().value to insert row and value
On Sun, 3 Mar 2013 07:14:10 +0000, TimLeonard wrote:
4. Any row on the CompareData sheet for which the MergedAddress does not exist on the Summary sheet is a "Deletion" 4. This one is a little tricky or misleading. In one case the PanelData/CompareData sheet could have a device not on the summary sheet due to having to add additional devices in the field, which will eventually be populated on the summary sheet but not until the as-built of the drawings and update of the summary sheet. In another case the field technician could delete more devices and the engineer expected and thus the summary sheet would not reflect this. In this case it truly would be a deletion that would need to be reflected on the summary sheet... So it should have the option to update the summary sheet with the differences found if possible Hmmm. What you write leads me to believe I have been laboring under a fundamental incorrect assumption. I have been assuming that, since you wrote that PanelData was derived from data "exported from a field panel and only contains what is programmed in the panel", We are generating CompareData from PanelData We are generating Summary from CompareData "Summary sheet in theory, should have all the programmed rows/cells populated the same as the CompareData" that we could regenerate Summary at any time, retaining changes that had been made manually. (after reviewing for errors and so forth). So I would intend to generate a fresh Summary sheet (retaining the appropriate additions) whenever a new PanelData sheet was downloaded (and a new CompareData sheet generated). In other words, I assumed it should never be the case, with a freshly generated Summary sheet, that there could be rows on CompareData/Panel Data which are "waiting" to be placed on the Summary sheet until the "as-built drawings". The methodology also assumes that we will generate a fresh Summary sheet whenever PanelData is updated. That being the case, the concept of data on CompareData that is not on Summary representing deletions should hold. Please clarify. |
#84
|
|||
|
|||
Quote:
Quote:
Only what has been programmed in the panel OR Deleted from the panel will be in the PanelData and thus on the CompareData Quote:
Here is a good example...The job could be a full floor in an office building that gets completely demo'd at the same time the engineer is designing the remodel. So the engineer has taken the floor plan and determined which devices need to be deleted from the summary sheet. He now merges the new floorplan on the drawing and begins placing the required devices on the drawing. These new devices have different devicelabels and maybe extendedlabels. Now during the field inspection the technician is required to install another device...under my current process, this device would then be on the compareData sheet and not on the Summary sheet since it did not start with the engineer... I think the fix is rather than waiting to asbuilt the device, I think it needs to to have a process change that will avoid ever having a device on the compareData that is not on the Summary Sheet...Perhaps having the technician call the engineer to get the device number and thus populating the summary sheet with the added device, this would prevent the comparedata sheet from having a device that the summary doesn't. So if we are creating the summarysheet each time the comparedata sheet is created and it keeps the manually entered data then I think we are accomplishing the need end result as long as I make the process change stated above... However what will happen when comparing the comparedata and summary sheets and the labels don't match. The comparedata labels should take precedence since it is what is programmed in the field. will there be an option to update or will it automatically do the update when it recreates the summary sheet?? |
#85
Posted to microsoft.public.excel.programming
|
|||
|
|||
leading zeros using ActiveCell.Offset().value to insert row and value
On Mon, 4 Mar 2013 04:04:28 +0000, TimLeonard wrote:
So if we are creating the summarysheet each time the comparedata sheet is created and it keeps the manually entered data then I think we are accomplishing the need end result as long as I make the process change stated above... OK, sounds good. However what will happen when comparing the comparedata and summary sheets and the labels don't match. The comparedata labels should take precedence since it is what is programmed in the field. will there be an option to update or will it automatically do the update when it recreates the summary sheet?? Right now, whenever we create a Summary sheet, the sequence is: 1. Create a CompareData sheet from the existing PanelData sheet in the workbook 2. Copy the CompareData sheet to the Summary sheet. 3. Format the Summary sheet appropriately. So the answer is that the CompareData sheet will take precedence, by design. What we will need to decide is what changes in the Summary sheet we want to retain. It may be that a list of valid changes/additions/deletions will need to be provided to someone, who can make the changes in the Panel. In any event, here is another batch of modules: http://sdrv.ms/XHKG9Q Let me know how they work for you. CreateCompareDataSheet works as before CreateSummarySheet is similar, except I am allowing selection of the locked cells (still can't change them). It occurred to me that for the engineer to make a manual entry, he would need to navigate to the appropriate line. I'm not sure of the best way to do this, but by allowing selection of the locked cells, it is possible to use Excel's "Find & Select" mechanism to get there. They can now Find and Goto a particular merged address, or Device Address, etc. If you have other thoughts, let me know. CompareDataVsSummary checks to see if there a Added Devices (on Summary sheet but not on CompareData sheet) Changed Devices (merely checks of any differences in the editable cells between the two sheets) Deleted Devices (on CompareData but not on Summary) -- the logic of this latter we have discussed. It then formats the output so as to enable relatively easy analysis. I'm sure there are better ways to format the output, and am open to any suggestions that you think might make analysis of the data more clear. There is also an AddLoops macro which does not really allow adding anything, but is merely a very preliminary thought on how to allow user modification of the Summary sheet in terms of adding loops or nodes. It's just there as a trial, and I'm not happy with it. It presents an input box to the user asking what node he wants to add (or add loops to); then it presents him with a list of all the loops in that node (0 to 10), and how many addresses are free. I included a node 0 (which I know is not a real node) because in my testing, I didn't always have zones; and I have each loop having 159*2 or 318 possible addresses. What I'd like to do is a single dialog box where the node is selected (perhaps with a spinner) and then multiple loops would be allowed to be chosen at the same time (check boxes with multiple selections allowed). |
#86
|
||||
|
||||
Quote:
Also I sure your aware but if the create summary sheet macro is ran twice it overwrites any manual entered devices. Perhaps this is because we have not retained anything yet Quote:
Quote:
Also would it be possible to order these as "Added Devices" "Deleted Devices" and then "Changed Devices" there will be more adds and deletes then changes... Quote:
|
#87
Posted to microsoft.public.excel.programming
|
|||
|
|||
leading zeros using ActiveCell.Offset().value to insert row and value
On Wed, 6 Mar 2013 00:40:35 +0000, TimLeonard wrote:
HMMM. For some reason, I am only now seeing this response of yours. I hate to post my email address in public, but later I'll put it reversed, with extra spaces, and you should make the obvious changes. mo c.e nil nod lef nes orT Ano r I will respond to your comments below after I've had a chance to look at them :-( For now, I have come up with a dialog box to enable Adding Loops. The engineer will select a node and then have the opportunity to select one or more loops for that node. The loops will have a list of the "Free Device Addresses" (i.e. it'll be 159 if either none have been assigned, or the loop does not exist). Also, the loop will be not-selectable if it already exists. Right now, it's just a GUI and doesn't result in anything happening. But that will come soon. Please remove any modules I have previously sent, and insert the ones at this link: http://sdrv.ms/14Dfo51 Sorry about the delay and I don't know why I did not see it until now. What we will need to decide is what changes in the Summary sheet we want to retain. It may be that a list of valid changes/additions/deletions will need to be provided to someone, who can make the changes in the Panel. I think any row that has a value in the "Project Number" column should be retained. Since this repersents the Engineered Adds and Deletes waiting to be programmed in the field. Also if a device is deleted (wiped out) on the summary sheet the Device Type does not clear...(Correction, if you delete one line at a time is works fine but not with multiple lines) Also I sure your aware but if the create summary sheet macro is ran twice it overwrites any manual entered devices. Perhaps this is because we have not retained anything yet It occurred to me that for the engineer to make a manual entry, he would need to navigate to the appropriate line. I'm not sure of the best way to do this, but by allowing selection of the locked cells, it is possible to use Excel's "Find & Select" mechanism to get there. They can now Find and Goto a particular merged address, or Device Address, This will be fine, its what they are use to using... CompareDataVsSummary checks to see if there a Added Devices (on Summary sheet but not on CompareData sheet) Changed Devices (merely checks of any differences in the editable cells between the two sheets) Deleted Devices (on CompareData but not on Summary) -- the logic of this latter we have discussed. It then formats the output so as to enable relatively easy analysis. I'm sure there are better ways to format the output, and am open to any suggestions that you think might make analysis of the data more clear. This works I just need to play with it more, but for some reason deletes the contents of the CompareData sheet after executing... Also would it be possible to order these as "Added Devices" "Deleted Devices" and then "Changed Devices" there will be more adds and deletes then changes... There is also an AddLoops macro which does not really allow adding anything, but is merely a very preliminary thought on how to allow user modification of the Summary sheet in terms of adding loops or nodes. (Snipped) What I'd like to do is a single dialog box where the node is selected (perhaps with a spinner) and then multiple loops would be allowed to be chosen at the same time (check boxes with multiple selections allowed). I think the engineers will be open to anything for this... +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ |
#88
Posted to microsoft.public.excel.programming
|
|||
|
|||
leading zeros using ActiveCell.Offset().value to insert row and value
On Wed, 6 Mar 2013 00:40:35 +0000, TimLeonard wrote:
What we will need to decide is what changes in the Summary sheet we want to retain. It may be that a list of valid changes/additions/deletions will need to be provided to someone, who can make the changes in the Panel. I think any row that has a value in the "Project Number" column should be retained. Since this repersents the Engineered Adds and Deletes waiting to be programmed in the field. Also if a device is deleted (wiped out) on the summary sheet the Device Type does not clear...(Correction, if you delete one line at a time is works fine but not with multiple lines) Fixed and will be in the next set of macros I send you. Also I sure your aware but if the create summary sheet macro is ran twice it overwrites any manual entered devices. Perhaps this is because we have not retained anything yet Correct -- That part has not been coded yet. It then formats the output so as to enable relatively easy analysis. I'm sure there are better ways to format the output, and am open to any suggestions that you think might make analysis of the data more clear. This works I just need to play with it more, but for some reason deletes the contents of the CompareData sheet after executing... I cannot reproduce that phenomenon. There should be no reason for the contents of CompareData to be deleted after running CompareDataVsSummary CompareData contents gets deleted prior to its being created (and it gets recreated whenever we create the Summary sheet), but it should not be being cleared at other times. Please post (or email) a copy of a misbehaving workbook so I may look at it more closely. I may need the exact steps you took to cause the contents of CompareData to be cleared. Also would it be possible to order these as "Added Devices" "Deleted Devices" and then "Changed Devices" there will be more adds and deletes then changes... Done What I'd like to do is a single dialog box where the node is selected (perhaps with a spinner) and then multiple loops would be allowed to be chosen at the same time (check boxes with multiple selections allowed). I think the engineers will be open to anything for this... A preliminary version, which only presents the Dialog box, is with the last set of macros I provided. Let me know what you think. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace leading zeros with leading spaces ? | Excel Programming | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
How do I insert leading zeros? | New Users to Excel | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) | |||
Insert Leading Zeros | Excel Worksheet Functions |