No attribution, so I assume it’s David Hagar
EXCEL 2000 TIP
An intriguing property was added to the Range object in Excel 2000. It
is the ID property. In the normal scheme of things, it assigns a string
to a worksheet cell, which is used in a HTML tag when the worksheet is
saved as a web page. If the worksheet is saved in a normal manner, the
ID does not appear to be persistent. However, if ID’s of cells are set
when a workbook is opened, they can be used in some interesting ways.
As an example, consider the following:
With Sheets(1)
.Range(“a1”).ID = “Test”
End With
End Sub
‘in Sheet1 module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.ID <> “” Then
MsgBox Target.ID
End If
End Sub
In this case, if A1 is selected, the MsgBox dialog will appear with
the text “Test”. Thus, this provides a method of creating cell comments
that do not have to be stored with the cells. Further, if the assignment
of ID strings to cells is criteria-based rather than address-based, then
this provides a dynamic method of documenting cells of varying properties.
Another use of the ID property would be to apply the Collection class
for the Tag property developed by Ken Getz and Mike Gilbert (shown in the
Nov ’98 issue of MOD magazine, p.36) to this system. I leave that as an
exercise to the reader.
Editor’s Note: It’s too bad this property doesn’t persist. Maybe when Excel goes XML, it will.
How do you obtain the EEE? The only thing I can find on the net is an archive on j-walk’s site.
Billkamm: It’s not written any more. J-Walk has been hosting the archives since the beginning, but he and David agreed to allow me to reproduce them here. I test the solution, provide some examples and commentary, but otherwise don’t really add much value to them. It’s just an attempt to get the information out to a group of people who may not have otherwise seen them.
DIck – Speaking about EEE. What was the main reason for not continuing to publish it?
Yes, I learned a lot from the EEE-letters :)
Kind regards,
Dennis
Dennis: I don’t know, but I assume it’s just like any charity project. Real life gets in the way and you have to make choices about how to spend your time.
I personally believe that submitter’s bag of tricks were exhausted
The experts in this blog should get together and write a new EEE.
Although, this blog in a way is kind of like an EEE already.
Dear dailydoseofexcel,
The cell K2 = sum( I2*AS2)
in which
I2= 20871 where the forumla here is I2=SUM(G2+H2)
AS2 =0.077 – where the forumla here is
=IF(ISERROR((AG2/(AG2+AJ2+AM2)*AD2)*A2*1.05/840/AF2), “”,((AG2/(AG2+AJ2+AM2)*AD2)*A2*1.05/840/AF2))
So, the correct result should be K2= 1607.067.
Why it calculates to the cell of K2 result return now is
1598.588 – it is wrong answer.
For details, pls see the wrapwrepusage.zip file
It is a big different.
Pls instruct !
thanks
gary
mY EMAIL: garywwww3@hotmail.com
Not quite sure how this K2, I2, whatever problem fits in with the ID discussion but…
I suspect K2 shows 0.077 as the result of the format/column width settings. The actual number is probably closer to 0.076593743.
Also, =SUM(I2*AS2) is unnecessary. Just =I2*AS2 will do.
OK, guys.
Last time I looked, I could not access the Getz & Gilbert article. Can’t find it again now. Anyone have a link?
worksheet A
cell F name is called product code
cell F2 is Q921
cell F3 is Q5201
cell G name is called yarn count
but
worksheet B
cell A name is called product code same as cell F of worksheetA
Cell B name is called yarn count same as cell G of worksheetA
if user plugin cell B62 with value 1234 and cell A62 at worksheet B is
equal to worksheetA cell F2.
How it link up and automatically display on G2 and the rest…
but must be able to match and identify the product code of worksheet A
and B
in order for user to plugin lots of different number on worksheetB-
column cellB
and it will display back to worksheetA – column cellG
Is vlookup and please give formula.
thanks
please instruct!
thanks
garywwww3@hotmail.com