Printing a Lines of Data

[Guest]

I have entered into a spreadsheet many lines of data using one line per
person to include information for that person. I wish to be able to select a
person by number and then print that persons data onto a form with the data
in pre planned positions on the Form, aAlso I would like to display the same
data on a form on the screen. Can this be done with Excel if so can you give
me some guidance and help.
Dave T

 

[Earl Kiosterud]

Toby,

In a word, no. Not directly, anyway. If you have Access, link your table to Access, then
create a report with the layout you want. Or use the Word mail merge.

Your table layout of the data is correct for when you want to do analysis and summary kinds
of things. That's what Excel is good for. But it doesn't have means to present the data in
other layouts.

If you still want the single-record layout on a sheet (which you could also print -- Excel
prints a sheet as is), create another sheet, using a VLOOKUP for each field. Be sure your
key field (name, or whatever uniquely identifies a person) is at the left of the other
fields.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Pete_UK]

Use a separate sheet for the Form, and enter the person number into a
cell (eg B5). These numbers must be unique and should be in the left-
most column of your main data table (eg in column A, and assume your
data is on a sheet called Data). You can then use a formula like this
in the Form sheet, maybe in cell B7:

=VLOOKUP($B$5,Data!$A$2:$F$100,2,0)

where I have assumed that your data table occupies columns A to F in
the Data sheet and rows 2 to 100 - adjust as necessary.

The third parameter of the VLOOKUP function relates to the column in
the table where the data should be returned from if a match is found.
In this case it is 2, so it will get data from column B of the table
(which might be the person's name). This is the only parameter that
needs to be changed when you copy this formula to other cells on the
Form. So, if you copy it to B9, for example, you might want the data
from the 3rd column to appear there, so change the formula to:

=VLOOKUP($B$5,Data!$A$2:$F$100,3,0)

and similarly for the other columns of data.

Once your Form is fully set up, and you have formatted the sheet how
you want it to appear, with labels etc., then all you will need to do
is change the person number in B5 and all the other details will
appear for that person (as long as there is a match - you will get #N/
A everywhere if the person number in B5 does not match any of those in
your table).

Hope this helps.

Pete

 

[Guest]

Thanks guys I wiil try to follow your suggestions and possibly get back to
you if I run into trouble.