 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2007Can I attach an Excel file to a post?
My problem can only be properly understood by you actually seeing the worksheets Is there a way to achieve this? The group doesn't like to see attachments, partly because of virus potential, partly because of the download impact. Try and explain it, you might be surprised. If it is not clear, someone might then be prepared to exchange the file with you, off-group.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > My problem can only be properly understood by you actually seeing the > worksheets > Is there a way to achieve this? Here goes .. Windows XP, Office 2003 I currently have a telephone index, column headings, "Name", "Home", "Cell", "Business" and "Fax" +- 300 entries, one in each row. The numbers are not spaced like telephone numbers (555 555 5555) but are 5555555555. Some Names have only one number, e.g. a Home number only, some have two, some have three and others have all four numbers. (one in each column) I need to convert this list to a narrow booklet page, each entry to span 5 rows, as follows: A1 The actual name (of the first person on the current list) B2 "Home", C2 The actual Home number, or blank if no home number B3 "Cell", C3 The actual Cell number, or blank if no cell number B4 "Bus", C4 The actual Business number, or blank if no business number B5 " Fax", B5 The actual Fax number, or blank if no fax number B6 the actual name (of the second person on the current list), and so on. In other words: In column A, An actual name, in rows 1, 6, 11, 16 and so on In column B, "Home" in rows 2, 7, 12, 17 and so on In column B, "Cell" in rows 3, 8, 13, 18 and so on In column B, "Bus" in rows 4, 9, 14, 19 and so on In column B, "Fax" in rows 5, 10, 15, 20 and so on In column C, The actual Home number (or blank) in rows 2, 7, 12, 17 and so on In column C, The actual Cell number (or blank) in rows 3, 8, 13, 18 and so on In column C, The actual Business number (or blank) in rows 4, 9, 14, 19 and so on In column C, The actual Fax number (or blank) in rows 5, 10, 15, 20 and so on Feint line border lines across cols A, B, and C (not down) between lines 5&6, between lines 10&11, between lines 15&16 All other lines across cols A, B and C (not down) to have even feinter (40% grey?) lines between each line. Each entry would look something like this: ___________________________ Jones, Peter Home 123 456 7890 Cell 246 802 468 Bus (Blank - he does not have a business 'phone) Fax 555 555 5555 Clear as mud? > The group doesn't like to see attachments, partly because of virus > potential, partly because of the download impact. [quoted text clipped - 5 lines] >> worksheets >> Is there a way to achieve this? Let's say that your datalist is on Sheet1, and you're creating this "Booklet" on another sheet in the same WB. As you describe, your Sheet1 labels are in Row1, and your data starts in Row2. You'll notice that there is primarily *ONE* formula returning all your data to the booklet sheet, with just Columns being the main revision to each. In the booklet sheet: in A1 enter: =INDEX(Sheet1!$A$2:$A$300,ROWS($1:5)/5) In B2 enter: Home In B3 enter: Cell .In B4 enter: Business In B5 enter: Fax In C2 enter: =IF(INDEX(Sheet1!$B$2:$B$300,ROWS($1:5)/5)="","",TEXT(INDEX(Sheet1!$B$2:$B$300,ROWS($1:5)/5),"### ### ####")) In C3 to C5, enter the same formula just *changing* the Column references, as: =IF(INDEX(Sheet1!$C$2:$C$300,ROWS($1:6)/5)="","",TEXT(INDEX(Sheet1!$C$2:$C$300,ROWS($1:6)/5),"### ### ####")) =IF(INDEX(Sheet1!$D$2:$D$300,ROWS($1:7)/5)="","",TEXT(INDEX(Sheet1!$D$2:$D$300,ROWS($1:7)/5),"### ### ####")) =IF(INDEX(Sheet1!$E$2:$E$300,ROWS($1:8)/5)="","",TEXT(INDEX(Sheet1!$E$2:$E$300,ROWS($1:8)/5),"### ### ####")) NOW ... select A1 to C5, and drag down the selection to copy as far as needed. If your data goes beyond Row 300 in Sheet1, you'll have to adjust the Index references. SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Here goes .. Windows XP, Office 2003 I currently have a telephone index, column headings, "Name", "Home", "Cell", "Business" and "Fax" +- 300 entries, one in each row. The numbers are not spaced like telephone numbers (555 555 5555) but are 5555555555. Some Names have only one number, e.g. a Home number only, some have two, some have three and others have all four numbers. (one in each column) I need to convert this list to a narrow booklet page, each entry to span 5 rows, as follows: A1 The actual name (of the first person on the current list) B2 "Home", C2 The actual Home number, or blank if no home number B3 "Cell", C3 The actual Cell number, or blank if no cell number B4 "Bus", C4 The actual Business number, or blank if no business number B5 " Fax", B5 The actual Fax number, or blank if no fax number B6 the actual name (of the second person on the current list), and so on. In other words: In column A, An actual name, in rows 1, 6, 11, 16 and so on In column B, "Home" in rows 2, 7, 12, 17 and so on In column B, "Cell" in rows 3, 8, 13, 18 and so on In column B, "Bus" in rows 4, 9, 14, 19 and so on In column B, "Fax" in rows 5, 10, 15, 20 and so on In column C, The actual Home number (or blank) in rows 2, 7, 12, 17 and so on In column C, The actual Cell number (or blank) in rows 3, 8, 13, 18 and so on In column C, The actual Business number (or blank) in rows 4, 9, 14, 19 and so on In column C, The actual Fax number (or blank) in rows 5, 10, 15, 20 and so on Feint line border lines across cols A, B, and C (not down) between lines 5&6, between lines 10&11, between lines 15&16 All other lines across cols A, B and C (not down) to have even feinter (40% grey?) lines between each line. Each entry would look something like this: ___________________________ Jones, Peter Home 123 456 7890 Cell 246 802 468 Bus (Blank - he does not have a business 'phone) Fax 555 555 5555 Clear as mud? > The group doesn't like to see attachments, partly because of virus > potential, partly because of the download impact. [quoted text clipped - 5 lines] >> worksheets >> Is there a way to achieve this? As far as those lines you mentioned, just add them to that first block of formulas (A1 to C5) as you wish, and they'll be copied down the sheet with the rest of the formulas. SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Let's say that your datalist is on Sheet1, and you're creating this "Booklet" on another sheet in the same WB. As you describe, your Sheet1 labels are in Row1, and your data starts in Row2. You'll notice that there is primarily *ONE* formula returning all your data to the booklet sheet, with just Columns being the main revision to each. In the booklet sheet: in A1 enter: =INDEX(Sheet1!$A$2:$A$300,ROWS($1:5)/5) In B2 enter: Home In B3 enter: Cell .In B4 enter: Business In B5 enter: Fax In C2 enter: =IF(INDEX(Sheet1!$B$2:$B$300,ROWS($1:5)/5)="","",TEXT(INDEX(Sheet1!$B$2:$B$300,ROWS($1:5)/5),"### ### ####")) In C3 to C5, enter the same formula just *changing* the Column references, as: =IF(INDEX(Sheet1!$C$2:$C$300,ROWS($1:6)/5)="","",TEXT(INDEX(Sheet1!$C$2:$C$300,ROWS($1:6)/5),"### ### ####")) =IF(INDEX(Sheet1!$D$2:$D$300,ROWS($1:7)/5)="","",TEXT(INDEX(Sheet1!$D$2:$D$300,ROWS($1:7)/5),"### ### ####")) =IF(INDEX(Sheet1!$E$2:$E$300,ROWS($1:8)/5)="","",TEXT(INDEX(Sheet1!$E$2:$E$300,ROWS($1:8)/5),"### ### ####")) NOW ... select A1 to C5, and drag down the selection to copy as far as needed. If your data goes beyond Row 300 in Sheet1, you'll have to adjust the Index references. SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Here goes .. Windows XP, Office 2003 I currently have a telephone index, column headings, "Name", "Home", "Cell", "Business" and "Fax" +- 300 entries, one in each row. The numbers are not spaced like telephone numbers (555 555 5555) but are 5555555555. Some Names have only one number, e.g. a Home number only, some have two, some have three and others have all four numbers. (one in each column) I need to convert this list to a narrow booklet page, each entry to span 5 rows, as follows: A1 The actual name (of the first person on the current list) B2 "Home", C2 The actual Home number, or blank if no home number B3 "Cell", C3 The actual Cell number, or blank if no cell number B4 "Bus", C4 The actual Business number, or blank if no business number B5 " Fax", B5 The actual Fax number, or blank if no fax number B6 the actual name (of the second person on the current list), and so on. In other words: In column A, An actual name, in rows 1, 6, 11, 16 and so on In column B, "Home" in rows 2, 7, 12, 17 and so on In column B, "Cell" in rows 3, 8, 13, 18 and so on In column B, "Bus" in rows 4, 9, 14, 19 and so on In column B, "Fax" in rows 5, 10, 15, 20 and so on In column C, The actual Home number (or blank) in rows 2, 7, 12, 17 and so on In column C, The actual Cell number (or blank) in rows 3, 8, 13, 18 and so on In column C, The actual Business number (or blank) in rows 4, 9, 14, 19 and so on In column C, The actual Fax number (or blank) in rows 5, 10, 15, 20 and so on Feint line border lines across cols A, B, and C (not down) between lines 5&6, between lines 10&11, between lines 15&16 All other lines across cols A, B and C (not down) to have even feinter (40% grey?) lines between each line. Each entry would look something like this: ___________________________ Jones, Peter Home 123 456 7890 Cell 246 802 468 Bus (Blank - he does not have a business 'phone) Fax 555 555 5555 Clear as mud? > The group doesn't like to see attachments, partly because of virus > potential, partly because of the download impact. [quoted text clipped - 5 lines] >> worksheets >> Is there a way to achieve this? AMAZING!!!!! It works, except for two small items: 1 All of my numbers are either preceded by a single zero (local) or two zeros (international). These zeros are not being brought to the booklet sheet. The original sheet is formatted as "Text" - no formulas there, and the booklet sheet as "General". If I change the booklet sheet to "Text" the formulas appear... 2. Despite your "####" in the formulas as the last four digits, the result is three digits at the end of each number... Some of the numbers have more than 10 digits - Can I add more "###"s at the front of the #s? > Let's say that your datalist is on Sheet1, and you're creating this > "Booklet" on another sheet in the same WB. [quoted text clipped - 105 lines] >>> worksheets >>> Is there a way to achieve this? Play around! Try and/or change these: "000 000 0000" "000 000 0000#" "000 000 0000##" SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== AMAZING!!!!! It works, except for two small items: 1 All of my numbers are either preceded by a single zero (local) or two zeros (international). These zeros are not being brought to the booklet sheet. The original sheet is formatted as "Text" - no formulas there, and the booklet sheet as "General". If I change the booklet sheet to "Text" the formulas appear... 2. Despite your "####" in the formulas as the last four digits, the result is three digits at the end of each number... Some of the numbers have more than 10 digits - Can I add more "###"s at the front of the #s? > Let's say that your datalist is on Sheet1, and you're creating this > "Booklet" on another sheet in the same WB. [quoted text clipped - 105 lines] >>> worksheets >>> Is there a way to achieve this? Quite a clear explanation - what's the fuss about? <bg> I see you have put spaces in the phone numbers in your example - will these always occur after the 3rd and 6th digit of the phone number? Can you ensure that there are no spaces in your original phone numbers, and are these stored as text (to preserve leading zeroes)? Given that you require borders, this would necessitate a VBA solution - are you comfortable with this? Pete > Here goes .. Windows XP, Office 2003 > [quoted text clipped - 66 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.