Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a form with only a certain amount of rows used
I am trying to make a form in excel 2002 that has the following
characteristics. I enter data on one worksheet "Data Entry" consisting of name, address, ssn, address, monies owed, interest and total wages owed. The data entered can be for one person or 300 plus people. All data is confined to one row per person. I have a final version of this "Wage Summary" located on another tab in the workbook and link information from the "Data Entry" into a corresponding row in the "Wage Summary". My problem is if I only have two or three, I don't want to print 300 blank rows on the "Wage Summary" sheet. Right now I have different "Wage Summary" sheets linking 15, 30, 45, 60 and 96 persons and I am required to select the appropriate worksheet for them to fit. I need to have one "Wage Summary" that will show either the one or two persons owed wages or the 100 people or whatever amount of persons I have, using only one final worksheet to print (Wage Summary) Is there some type of programming that will look up and use only the data that is found and size the final product accordingly? Can someone give me some guidance with this. My skills are medium in excel. Thank you Bob Reynolds |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a form with only a certain amount of rows used
Bob,
link information from the "Data Entry" into a corresponding row Sound like you have a direct link between sheets? e.g. A1 in the Data Entry sheet has a formula like Wage Summary!A1 There are many things that you can do (you did want ideas?) A VLookup is the first thing that comes to mind. As you enter names in the Wage Summary, the lookup would get the corresponding data from the Data Entry sheet based on the name. (SSN number would be a better lookup though). For some help on lookups, try he http://www.mvps.org/dmcritchie/excel/vlookup.htm You could also AutoFilter the Wage Summary sheet and select Non Blank cells as the filter. Look at AutoFilter and Advanced Filter on this site: http://www.contextures.com/tiptech.html If you wanted to go the VBA route, there's even more possibilities. Without knowing how your sheets are set up, it's difficult to offer any specific advice. John "Bob Reynolds" wrote in message ... I am trying to make a form in excel 2002 that has the following characteristics. I enter data on one worksheet "Data Entry" consisting of name, address, ssn, address, monies owed, interest and total wages owed. The data entered can be for one person or 300 plus people. All data is confined to one row per person. I have a final version of this "Wage Summary" located on another tab in the workbook and link information from the "Data Entry" into a corresponding row in the "Wage Summary". My problem is if I only have two or three, I don't want to print 300 blank rows on the "Wage Summary" sheet. Right now I have different "Wage Summary" sheets linking 15, 30, 45, 60 and 96 persons and I am required to select the appropriate worksheet for them to fit. I need to have one "Wage Summary" that will show either the one or two persons owed wages or the 100 people or whatever amount of persons I have, using only one final worksheet to print (Wage Summary) Is there some type of programming that will look up and use only the data that is found and size the final product accordingly? Can someone give me some guidance with this. My skills are medium in excel. Thank you Bob Reynolds |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a form with only a certain amount of rows used
Thank you John,
I do have a direct link as they are in the same workbook. I use the "dataentrysheet" to enter all the data and I will show you an example of the data I need to "sort" on the final page. EXAMPLE: FName, LName, SSN, Address, Payday, Workweekending, Wages due, Interest due, Total monies owed. The data in the example may consist of one row or many rows and this is where I need the information to be linked to the other worksheet. I have provided the first couple of rows on the worksheet here. NAME & SSN ADDRESS PAYDAY (Begin) PAYDAY (End) Date Original Interest Calc To: (D) Vacation ($) (V) Interest from Computation (I) BACK WAGES ($) (BW) Total Wages Due TWD = (V+BW) Coeff ( C ) INTEREST ($) (I) + (TWD * C) TOTAL DUE ($) Weeks Late jackson, andrew 222-22-2222 123 everywehre anyplace, nc 22222 03-03-01 03-03-01 03-03-01 $5.00 $565.00 $565.00 0.0815 $51.05 $616.05 53 This is the way data is entered, and the weeks late at the end is the result of a vlookup for an interest sheet. The above data, for each row where there is data needs to be linked over to a "summary sheet" that has a preheader and footer on it. Right now I have seperate ones like I said, but would need to have one sheet that will only use the space required.... Hope this clears it up, and I have no objection to sending you a copy of the workbook if it would help. I remember seeing someplace a formula like the labels template that uses something like that, but it's beyond my skills right now. I'm looking on the two sites you mentioned and will see how it works. Thanks so much Bobv Reynolds "John Wilson" wrote in message ... Bob, link information from the "Data Entry" into a corresponding row Sound like you have a direct link between sheets? e.g. A1 in the Data Entry sheet has a formula like Wage Summary!A1 There are many things that you can do (you did want ideas?) A VLookup is the first thing that comes to mind. As you enter names in the Wage Summary, the lookup would get the corresponding data from the Data Entry sheet based on the name. (SSN number would be a better lookup though). For some help on lookups, try he http://www.mvps.org/dmcritchie/excel/vlookup.htm You could also AutoFilter the Wage Summary sheet and select Non Blank cells as the filter. Look at AutoFilter and Advanced Filter on this site: http://www.contextures.com/tiptech.html If you wanted to go the VBA route, there's even more possibilities. Without knowing how your sheets are set up, it's difficult to offer any specific advice. John "Bob Reynolds" wrote in message ... I am trying to make a form in excel 2002 that has the following characteristics. I enter data on one worksheet "Data Entry" consisting of name, address, ssn, address, monies owed, interest and total wages owed. The data entered can be for one person or 300 plus people. All data is confined to one row per person. I have a final version of this "Wage Summary" located on another tab in the workbook and link information from the "Data Entry" into a corresponding row in the "Wage Summary". My problem is if I only have two or three, I don't want to print 300 blank rows on the "Wage Summary" sheet. Right now I have different "Wage Summary" sheets linking 15, 30, 45, 60 and 96 persons and I am required to select the appropriate worksheet for them to fit. I need to have one "Wage Summary" that will show either the one or two persons owed wages or the 100 people or whatever amount of persons I have, using only one final worksheet to print (Wage Summary) Is there some type of programming that will look up and use only the data that is found and size the final product accordingly? Can someone give me some guidance with this. My skills are medium in excel. Thank you Bob Reynolds |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a form with only a certain amount of rows used
In essence you have a database and an entry form. One way to handle this is to limit your entry form to a small range: On the Entry form sheet: A1:name, B1: address, C1:ssn, etc... A2 to G2 are the user entry cells In your auto_open routine, use the following code to restrict the user to only these cells: Sheets("DataEntry").scrollarea="A2:G2" On this sheet, have a command button that will copy these cells to the next blank row of your database. When you want to "Extract" records from your database, use autofilter or advanced filter to "view" the records of interest or to copy them to a third sheet where you can view and edit them or format them to fit your desired "Report" ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a form with only a certain amount of rows used
Bob,
If those websites didn't offer any help, forward the workbook to me directly and I'll take a look (delete the SSN and Address data but leave the columns). John "Bob Reynolds" wrote in message .. . Thank you John, I do have a direct link as they are in the same workbook. I use the "dataentrysheet" to enter all the data and I will show you an example of the data I need to "sort" on the final page. EXAMPLE: FName, LName, SSN, Address, Payday, Workweekending, Wages due, Interest due, Total monies owed. The data in the example may consist of one row or many rows and this is where I need the information to be linked to the other worksheet. I have provided the first couple of rows on the worksheet here. NAME & SSN ADDRESS PAYDAY (Begin) PAYDAY (End) Date Original Interest Calc To: (D) Vacation ($) (V) Interest from Computation (I) BACK WAGES ($) (BW) Total Wages Due TWD = (V+BW) Coeff ( C ) INTEREST ($) (I) + (TWD * C) TOTAL DUE ($) Weeks Late jackson, andrew 222-22-2222 123 everywehre anyplace, nc 22222 03-03-01 03-03-01 03-03-01 $5.00 $565.00 $565.00 0.0815 $51.05 $616.05 53 This is the way data is entered, and the weeks late at the end is the result of a vlookup for an interest sheet. The above data, for each row where there is data needs to be linked over to a "summary sheet" that has a preheader and footer on it. Right now I have seperate ones like I said, but would need to have one sheet that will only use the space required.... Hope this clears it up, and I have no objection to sending you a copy of the workbook if it would help. I remember seeing someplace a formula like the labels template that uses something like that, but it's beyond my skills right now. I'm looking on the two sites you mentioned and will see how it works. Thanks so much Bobv Reynolds "John Wilson" wrote in message ... Bob, link information from the "Data Entry" into a corresponding row Sound like you have a direct link between sheets? e.g. A1 in the Data Entry sheet has a formula like Wage Summary!A1 There are many things that you can do (you did want ideas?) A VLookup is the first thing that comes to mind. As you enter names in the Wage Summary, the lookup would get the corresponding data from the Data Entry sheet based on the name. (SSN number would be a better lookup though). For some help on lookups, try he http://www.mvps.org/dmcritchie/excel/vlookup.htm You could also AutoFilter the Wage Summary sheet and select Non Blank cells as the filter. Look at AutoFilter and Advanced Filter on this site: http://www.contextures.com/tiptech.html If you wanted to go the VBA route, there's even more possibilities. Without knowing how your sheets are set up, it's difficult to offer any specific advice. John "Bob Reynolds" wrote in message ... I am trying to make a form in excel 2002 that has the following characteristics. I enter data on one worksheet "Data Entry" consisting of name, address, ssn, address, monies owed, interest and total wages owed. The data entered can be for one person or 300 plus people. All data is confined to one row per person. I have a final version of this "Wage Summary" located on another tab in the workbook and link information from the "Data Entry" into a corresponding row in the "Wage Summary". My problem is if I only have two or three, I don't want to print 300 blank rows on the "Wage Summary" sheet. Right now I have different "Wage Summary" sheets linking 15, 30, 45, 60 and 96 persons and I am required to select the appropriate worksheet for them to fit. I need to have one "Wage Summary" that will show either the one or two persons owed wages or the 100 people or whatever amount of persons I have, using only one final worksheet to print (Wage Summary) Is there some type of programming that will look up and use only the data that is found and size the final product accordingly? Can someone give me some guidance with this. My skills are medium in excel. Thank you Bob Reynolds |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making a form with only a certain amount of rows used
It sounds like that you are creating a database of sorts on your Wage
Summary sheet. You could define a name to the database by "counting" the number of entries(non blank cells) and then use this count to define that database. Then define your print area to that database. "Bob Reynolds" wrote in message ... I am trying to make a form in excel 2002 that has the following characteristics. I enter data on one worksheet "Data Entry" consisting of name, address, ssn, address, monies owed, interest and total wages owed. The data entered can be for one person or 300 plus people. All data is confined to one row per person. I have a final version of this "Wage Summary" located on another tab in the workbook and link information from the "Data Entry" into a corresponding row in the "Wage Summary". My problem is if I only have two or three, I don't want to print 300 blank rows on the "Wage Summary" sheet. Right now I have different "Wage Summary" sheets linking 15, 30, 45, 60 and 96 persons and I am required to select the appropriate worksheet for them to fit. I need to have one "Wage Summary" that will show either the one or two persons owed wages or the 100 people or whatever amount of persons I have, using only one final worksheet to print (Wage Summary) Is there some type of programming that will look up and use only the data that is found and size the final product accordingly? Can someone give me some guidance with this. My skills are medium in excel. Thank you Bob Reynolds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Various amount of data when making a radar chart | Charts and Charting in Excel | |||
Formulas-Making a number a standard amount of digits | Excel Discussion (Misc queries) | |||
Making a form visible | Excel Discussion (Misc queries) | |||
HELP! Making A Form | Excel Worksheet Functions | |||
Making a new form | Excel Discussion (Misc queries) |