How to extract and use data?

[Susan]

I do excel sheet for gallery with listing of artwork and
prices. Is there a way to pull off the data for title,
artist, medium, and price to create cards to print out
for posting on wall by the paintings? I have MS Office
Professional Edition 2003.

I may be in the wrong newsgroup now but I appreciate any
direction.

Thanks,
Susan

 

[Frank Kabel]

Hi Susan
you may provide some more details
- some example data of your current entries (plain text - no
attachments please)
- based on these examples the desired splitting
- some information (rules) how to identify the different sub strings

 

[Susan]

Title Medium Tax ( 8.5%) Total Price
67 Sweet Violets Oil 125.00 10.63 $ 135.63
68 Spring Has Come Oil NFS 480.00
69 Signs of Spring #1 Oil 225.00 19.13 $ 244.13
70 Signs of Spring #2 Oil 75.00 6.38 $ 81.38
71 Signs of Spring #3 Oil 75.00 6.38 $ 81.38
72 Grandiflora Oil 300.00 25.50 $ 325.50
73 Magnolia Acrylic 125.00 10.63 $ 135.63
74 Waterlilies Oil NFS 250.00
75 Study of Shadows Oil NFS 475.00
76 Pink Rose Oil 250.00 21.25 $ 271.25
77 First of Spring Acrylic 125.00 10.63 $ 135.63
78 New Life In Old Oil 350.00 29.75 $ 379.75
79 Yellow Iris Oil NFS 125.00


The above is from worksheet that I created. There ought to be a way to pull
data from worksheet and create cards to post by paintings.
Is this something that could be done with Office Professional , or is this
delving into programming and I would need additional software?

Thanks for information!
Susan

 

[Dave R.]

Sure you can do this, with Excel or Access. You can have the table below,
and on some other sheet (or some other area) you would have a nice pretty
form containing formulas. You would enter some identifying characteristic
(the # on the leftmost column seems appropriate), and then the other parts
of that form would fill in based on that number.

If the number isn't something you want in the form, you can put that away
from the form but the formulas within the form would pull from that number.

Assume you have simplified data like:

Number Title Medium Price
1 Happy Oil 25.00
2 Sad Clay 30.00


Your form looks like:

Happy
Clay
25.00

.....
Formula for happy, assuming number to make form for is in A10:

=INDEX(B$1:B$2,MATCH(A$10,A$1:A$2,0))

the B1:B2 part is what returns the TITLE. Copy this formula down for the
MEDIUM and PRICE information, and change the column reference to suit (i.e.
from B$1:B$2 to C$1:C$2 for MEDIUM and D$1$2 for PRICE).

You can then format the cells containing the formulas (returning the
results) to make it look nice enough, then print that form area on a card.

 

[Anders S]

Hi Susan,

This is fairly easy if I understand you correctly. You need two worksheets, one for the art list and one for the page to be printed.

On the art sheet:
- select the list and type ArtList in the name box (usually just above cell A1)
or
- select the list and do Insert>Names>Define and type ArtList in the text box

On the print sheet:
- enter in cells
A2 (Title): =VLOOKUP($A$1,ArtList,2,FALSE)
A3 (Medium): =VLOOKUP($A$1,ArtList,3,FALSE)
A4 (Price): =VLOOKUP($A$1,ArtList,6,FALSE)
- cell A1 is the input cell, enter for example 75 in A1 and the data for "Study of Shadows" will be displayed in A2:A4.
- arrange the layout of the print sheet by dragging cells A1:A4 where you want them, and apply formatting to suit.

The artists name is not included in your example, but you can easily add it once you understand how this works.

As the list expands or shrinks you will have to adjust the named range (ArtList) accordingly, via Insert>Names>Define...

HTH
Anders Silven

 

[Susan]

Thanks! Artist name is in the header information. Can header information be
referenced? If not, no big problem. Also, is this limited to use within
Excel or can fields be referenced and used cross applications within Office?

Susan
Hi Susan,

This is fairly easy if I understand you correctly. You need two worksheets,
one for the art list and one for the page to be printed.

On the art sheet:
- select the list and type ArtList in the name box (usually just above cell
A1)
or
- select the list and do Insert>Names>Define and type ArtList in the text
box

On the print sheet:
- enter in cells
A2 (Title): =VLOOKUP($A$1,ArtList,2,FALSE)
A3 (Medium): =VLOOKUP($A$1,ArtList,3,FALSE)
A4 (Price): =VLOOKUP($A$1,ArtList,6,FALSE)
- cell A1 is the input cell, enter for example 75 in A1 and the data for
"Study of Shadows" will be displayed in A2:A4.
- arrange the layout of the print sheet by dragging cells A1:A4 where you
want them, and apply formatting to suit.

The artists name is not included in your example, but you can easily add it
once you understand how this works.

As the list expands or shrinks you will have to adjust the named range
(ArtList) accordingly, via Insert>Names>Define...

HTH
Anders Silven

 

[Anders S]

Hi again,

I assume the setup works, basically. The third argument to the VLOOKUP() function designates which column to extract from the source list so you will have to adjust the formulas to fit the list.

"Can header information be referenced?"
If you mean "Can I reference a column by header text instead of column number?", yes but from what I know it is not worth the effort in this application.

"can fields be referenced and used cross applications within Office"
Generally yes, but it is not trivial and it takes VBA programming.

Best regards
Anders Silven