DTP


 
Lively discussions on the graphic arts and publishing — in print or on the web


Go Back   Desktop Publishing Forum > General Discussions > Software

Reply
 
Thread Tools Display Modes
Old 09-24-2009, 06:18 AM   #1
LoisWakeman
Staff
 
LoisWakeman's Avatar
 
Join Date: Jan 2005
Location: Uplyme, Devon, England
Posts: 1,402
Default Excel guru wanted

I am making a little Office app to manage the results of our village flower show. Doing the spreadsheet that works out entry numbers and points awarded was easy, but I hope to extend it by printing out entry cards for each entry - this would have the entrants name and class number etc printed on it.

The spreadsheet is like this (except I can't get this darned editor to preserve my tabs, so have attached a sample as a tab delimited text file):
Class
# Entrant 1 2 3 4
1
Mrs S & Mr J Hallett y y
2 Mrs P Taylor
3 Mrs M Bohane y y
4 Mr D Edwards y

and I want to step through the rows and columns, finding each "y" (for entry made in this class), and using that cell as the base point, collect the entrants name, number, and class number from the same row and column. Once I have those values in an array, I can (I hope) insert them in my merge document.

My first question is, can this be done? I assume it must be possible, but my VBA is pretty basic (pardon the pun). The second is, can someone point me to a web page that describes the principle of stepping through cells in a given range and getting related cell values? Google was not my friend in this case.
Attached Files
File Type: txt test.txt (517 Bytes, 10 views)
LoisWakeman is offline   Reply With Quote
Old 09-24-2009, 07:12 AM   #2
Andrew B.
Staff
 
Andrew B.'s Avatar
 
Join Date: Jan 2005
Location: Los Angeles, California
Posts: 3,501
Default

You can step though cells without VB with a series of nested IIF() statements. But the the thing is, what do you want to end up with to print this from Excel. Because, frankly, I don't see Excel as suited for printing non-tabular data, unless you link to the spreadsheet using a report writer or database manager.
Andrew B. is offline   Reply With Quote
Old 09-24-2009, 07:19 AM   #3
donmcc
Member
 
Join Date: Feb 2005
Location: Sarnia, Canada
Posts: 1,122
Default

Not sure exactly what your goal is. Are you trying to total all the entries (not only Roses, but other classes on other sheets)? Will the names be in the same order for each sheet, if that it the case (it would make it easier).

I think this can be done, and perhaps without VBA.
donmcc is offline   Reply With Quote
Old 09-24-2009, 07:21 AM   #4
Steve Rindsberg
Staff
 
Join Date: Nov 2004
Posts: 6,712
Default

I've known Excel gurus and believe me, I'm no Excel guru. Goober, maybe.
But here's something that might get you started with stepping through a range of cells:

Sub thing()
Dim x As Long
With Application.ActiveWorkbook
With .Worksheets(1)
For x = 1 To 4
If UCase(Cells(x, 3)) = "Y" Then
MsgBox (Cells(x, 1) & vbTab & Cells(x, 2))
End If
Next
End With
End With
End Sub

You might also be able to do this w/o VBA, using just formulae:

=+IF(C1="y",A1,"")

This looks at the value in column 3 and if it's "y", copies the value in column 1 to the current cell.

Now it gets weird:

=+IF(C1="y",A1,IF(D1="y",A1,IF(E1="y",A1,"")))

If the value in column c is y, then use the value in a
Otherwise, if the value in column D is y, then use the value in a
Otherwise, if the value in column E is y, then use the value in a
Otherwise, make it blank.

If you copy this into another range of cells in the sheet, it'll populate it with the class numbers for anyone with a y in column c, d, e.

A bit of modification will get you the names in the next column over.

In fact, change the first formula to this and you can copy/paste it into each cell in the first two columns of the new target range:

=+IF($C1="y",A1,IF($D1="y",A1,IF($E1="y",A1,"")))

   
__________________
Steve Rindsberg
====================
www.pptfaq.com
www.pptools.com
and stuff
Steve Rindsberg is offline   Reply With Quote
Old 09-24-2009, 07:24 AM   #5
Steve Rindsberg
Staff
 
Join Date: Nov 2004
Posts: 6,712
Default

Quote:
Originally Posted by Andrew B. View Post
You can step though cells without VB with a series of nested IIF() statements. But the the thing is, what do you want to end up with to print this from Excel. Because, frankly, I don't see Excel as suited for printing non-tabular data, unless you link to the spreadsheet using a report writer or database manager.
Lois could use Word to merge data from Excel into a document and mergeprint.

   
__________________
Steve Rindsberg
====================
www.pptfaq.com
www.pptools.com
and stuff
Steve Rindsberg is offline   Reply With Quote
Old 09-24-2009, 07:36 AM   #6
Andrew B.
Staff
 
Andrew B.'s Avatar
 
Join Date: Jan 2005
Location: Los Angeles, California
Posts: 3,501
Default

True, and she did say she wanted to do a merge. I guess I forgot that right after I read it.
Andrew B. is offline   Reply With Quote
Old 09-24-2009, 08:52 AM   #7
LoisWakeman
Staff
 
LoisWakeman's Avatar
 
Join Date: Jan 2005
Location: Uplyme, Devon, England
Posts: 1,402
Default

The entrants will be repeated the same on each sheet, yes. But I can do the totals easily - what I want to do is extract, for each entry marked "y", the person who entered and the class number, so I can then use that in a mail merge to print out entry tickets
LoisWakeman is offline   Reply With Quote
Old 09-24-2009, 08:54 AM   #8
LoisWakeman
Staff
 
LoisWakeman's Avatar
 
Join Date: Jan 2005
Location: Uplyme, Devon, England
Posts: 1,402
Default

Well, Goober or not - that gives me a really good start - many thanks, Chief! Formulae look easier, right from the start.

Once I've had some time to mess up, I'll let you know how I get on.
LoisWakeman is offline   Reply With Quote
Old 09-24-2009, 08:55 AM   #9
LoisWakeman
Staff
 
LoisWakeman's Avatar
 
Join Date: Jan 2005
Location: Uplyme, Devon, England
Posts: 1,402
Default

Easily done! As I often think halfway up the stairs "Now, why was I going up to the bedroom?"
LoisWakeman is offline   Reply With Quote
Old 09-24-2009, 12:12 PM   #10
Steve Rindsberg
Staff
 
Join Date: Nov 2004
Posts: 6,712
Default

Quote:
Originally Posted by LoisWakeman View Post
Well, Goober or not - that gives me a really good start - many thanks, Chief! Formulae look easier, right from the start.

Once I've had some time to mess up, I'll let you know how I get on.
Great ...

Usually, I go about this from a different direction ... I have an add-in that nudges Excel awake and reads all the data from the chosen worksheet into an array where I can have my way with it ... merging it into PowerPoint files. Weird but fun and sometimes very useful.

   
__________________
Steve Rindsberg
====================
www.pptfaq.com
www.pptools.com
and stuff
Steve Rindsberg is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Vista SP1 testers wanted Kelvyn General Publishing Topics 1 01-24-2007 09:25 AM
Recruiting MS Office Guru/DTP TDP-DTP Business Matters 8 06-15-2006 03:22 AM
Postscript Printer Recommendation Wanted danakay Hardware & Gadgets 9 03-14-2006 12:37 PM
Unicode WIKI — help wanted ktinkel Fonts & Typography 6 09-01-2005 11:39 AM
Word vs Acrobat guru needed!! ktinkel Print Production & Automation 7 05-13-2005 08:51 AM


All times are GMT -8. The time now is 01:28 PM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Contents copyright 2004–2014 Desktop Publishing Forum and its members.