leading zeros using ActiveCell.Offset().value to insert row and value

[TimLeonard]

Hmmm. What you write leads me to believe I have been laboring under
fundamental incorrect assumption.

I don't really think so, Sounds like I just clouded up ou
understanding...Perhaps I just need to change or explain m
approach/logic better

I have been assuming that, since you wrote that
PanelData was derived from data "exported from a field panel and onl
contains what is programmed in the panel",
We are generating CompareData from PanelData

This is fact true...
Only what has been programmed in the panel OR Deleted from the pane
will be in the PanelData and thus on the CompareData

We are generating Summary from CompareData
"Summary sheet in theory, should have all the programmed rows/cell
populated the same as the CompareData"

that we could regenerate Summary at any time, retaining changes that ha
been made manually. (after reviewing for errors and so forth). So
would intend to generate a fresh Summary sheet (retaining th
appropriate additions) whenever a new PanelData sheet was downloade
(and a new CompareData sheet generated).

In other words, I assumed it should never be the case, with a freshl
generated Summary sheet, that there could be rows on CompareData/Pane
Data which are "waiting" to be placed on the Summary sheet until th
"as-built drawings".

The methodology also assumes that we will generate a fresh Summary shee
whenever PanelData is updated. That being the case, the concept of dat
on CompareData that is not on Summary representing deletions shoul
hold.

Please clarify.

Let me try to explain it this way...Perhaps we could liken the summar
sheet as a forever ongoing updating living spreadsheet. I say thi
because the engineer could have several jobs that their working on an
it could take several months before one of the jobs gets installed, a
times some jobs get installed in phases that require devices to b
demo'd/deleted and then re-installed during the remodel/buildout.

Here is a good example...The job could be a full floor in an offic
building that gets completely demo'd at the same time the engineer i
designing the remodel. So the engineer has taken the floor plan an
determined which devices need to be deleted from the summary sheet. H
now merges the new floorplan on the drawing and begins placing th
required devices on the drawing. These new devices have differen
devicelabels and maybe extendedlabels. Now during the field inspectio
the technician is required to install another device...under my curren
process, this device would then be on the compareData sheet and not o
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 i
needs to to have a process change that will avoid ever having a devic
on the compareData that is not on the Summary Sheet...Perhaps having th
technician call the engineer to get the device number and thu
populating the summary sheet with the added device, this would preven
the comparedata sheet from having a device that the summary doesn't.

So if we are creating the summarysheet each time the comparedata shee
is created and it keeps the manually entered data then I think we ar
accomplishing the need end result as long as I make the process chang
stated above...

However what will happen when comparing the comparedata and summar
sheets and the labels don't match. The comparedata labels should tak
precedence since it is what is programmed in the field. will there b
an option to update or will it automatically do the update when i
recreates the summary sheet?

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

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 are:
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).

 

[TimLeonard]

What we will need to decide is what changes in the Summary sheet we wan
to retain. It may be that a list of valid changes/additions/deletion
will need to be provided to someone, who can make the changes in th
Panel.
I think any row that has a value in the "Project Number" column shoul

be retained. Since this repersents the Engineered Adds and Delete
waiting to be programmed in the field. Also if a device is delete
(wiped out) on the summary sheet the Device Type does no
clear...(Correction, if you delete one line at a time is works fine bu
not with multiple lines)
Also I sure your aware but if the create summary sheet macro is ra
twice it overwrites any manual entered devices. Perhaps this is becaus
we have not retained anything yet

It occurred to me that for the engineer to make a manual entry, he woul
need to navigate to the appropriate line. I'm not sure of the best wa
to do this, but by allowing selection of the locked cells, it i
possible to use Excel's "Find & Select" mechanism to get there. The
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 editabl
cells between the two sheets)
Deleted Devices (on CompareData but not on Summary) -- the logic o
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 an
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 delete
the contents of the CompareData sheet after executing...
Also would it be possible to order these as "Added Devices" "Delete
Devices" and then "Changed Devices" there will be more adds and delete
then changes...

There is also an AddLoops macro which does not really allow addin
anything, but is merely a very preliminary thought on how to allow use
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 selecte
(perhaps with a spinner) and then multiple loops would be allowed to b
chosen at the same time (check boxes with multiple selections allowed).

I think the engineers will be open to anything for this..

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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.

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

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.