S
SouthAfricanStan
My problem can only be properly understood by you actually seeing the
worksheets
Is there a way to achieve this?
worksheets
Is there a way to achieve this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
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.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
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.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
S
SouthAfricanStan
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?
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?
R
RagDyeR
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.
--
HTH,
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?
"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.
--
HTH,
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?
R
RagDyeR
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.
--
HTH,
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.
--
HTH,
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?
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.
--
HTH,
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.
--
HTH,
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?
P
Pete_UK
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
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
S
SouthAfricanStan
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?
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?
R
RagDyeR
Play around!
Try and/or change these:
"000 000 0000"
"000 000 0000#"
"000 000 0000##"
--
HTH,
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?
Try and/or change these:
"000 000 0000"
"000 000 0000#"
"000 000 0000##"
--
HTH,
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?