two... two... two questions in one!

[Greg Liber]

HI!

while in the past I've stated I'm pretty competent in Excel this group
really does put me to shame...that being said I have a couple of
questions...

1) I'd like to restrict the data being entered into a column to just
01,02,04,05,06 and anything else would not be allowed. I used the
validation function but this just restricts the information to
01,1,02,2,04,4,05,5,06 & 6. I have cells elsewhere in the sheet formatted
to return a General Ledger account number based on the number inputted and
the only way I could figure out how to get the "IF" formula to work was to
format the input cells as text. Can this be done?

2) The next question is... I have another cell that I need to have an
employee number entered into otherwise I need the cell next to it to give me
an error message when you try to enter any information into it.
I.E. - cell A9 needs to have the employee number entered into it or when you
try to enter in info into cell B9 all it returns is an error message.

Any help that you can give me is and always be greatly appreciated!

Greg

 

[Frank Kabel]

Hi Greg

[snip]

1) I'd like to restrict the data being entered into a column to
just 01,02,04,05,06 and anything else would not be allowed. I used
the validation function but this just restricts the information to
01,1,02,2,04,4,05,5,06 & 6. I have cells elsewhere in the sheet
formatted to return a General Ledger account number based on the
number inputted and the only way I could figure out how to get the
"IF" formula to work was to format the input cells as text. Can this
be done?

Two ideas:
1. Restrict the entries to numbers ranging from 1-6 and format the
cells with the custom format '00'. This way you should see only your
desired numbers and can calculate with them

2. The other solution would requiere that your entries are text:
- format the cells as text
- enter the following validation:
=AND(LEN(A1)=2,LEFT(A1,1)="0",VALUE(RIGHT(A1,1)>=1,VALUE(RIGHT(A1,1)<=6
)

2) The next question is... I have another cell that I need to have
an employee number entered into otherwise I need the cell next to it
to give me an error message when you try to enter any information
into it.
I.E. - cell A9 needs to have the employee number entered into it or
when you try to enter in info into cell B9 all it returns is an error
message.

enter the following validation formula for cell B9:
=A9<>""
or if you want to test for real numbers in A9
=ISNUMBER(A9)

HTH
Frank

 

[Greg Liber]

Thanks!
As always you really come through!

Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I reference the
four digit employee number onto a page that I then sort to remove all the
blank rows, the employee number then becomes 2 from 0002 (if under 999)
which isn't too bad because I can format the columns to 0000 but it sorts
with a ton of blank rows in between. The sheet is then copied onto a new
form that I send to payroll so I cannot leave the blank rows. (The blank
rows are payroll items that aren't relevant to that employee's current
payroll information) The sorting and copying is all done with a macro if
that helps.

The question is can I sort or remove the blank rows another way that keeps
the employee number as a four digit number throughout the entire process?

Again I really do appreciate your help!

Greg

I do need the employee number to be 4 digits as

Hi Greg

[snip]

1) I'd like to restrict the data being entered into a column to
just 01,02,04,05,06 and anything else would not be allowed. I used
the validation function but this just restricts the information to
01,1,02,2,04,4,05,5,06 & 6. I have cells elsewhere in the sheet
formatted to return a General Ledger account number based on the
number inputted and the only way I could figure out how to get the
"IF" formula to work was to format the input cells as text. Can this
be done?

Two ideas:
1. Restrict the entries to numbers ranging from 1-6 and format the
cells with the custom format '00'. This way you should see only your
desired numbers and can calculate with them

2. The other solution would requiere that your entries are text:
- format the cells as text
- enter the following validation:
=AND(LEN(A1)=2,LEFT(A1,1)="0",VALUE(RIGHT(A1,1)>=1,VALUE(RIGHT(A1,1)<=6
)

2) The next question is... I have another cell that I need to have
an employee number entered into otherwise I need the cell next to it
to give me an error message when you try to enter any information
into it.
I.E. - cell A9 needs to have the employee number entered into it or
when you try to enter in info into cell B9 all it returns is an error
message.

enter the following validation formula for cell B9:
=A9<>""
or if you want to test for real numbers in A9
=ISNUMBER(A9)

HTH
Frank

 

[Frank Kabel]

Hi Greg
[snip]

Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I then sort
to remove all the blank rows, the employee number then becomes 2 from
0002 (if under 999) which isn't too bad because I can format the
columns to 0000 but it sorts with a ton of blank rows in between.
The sheet is then copied onto a new form that I send to payroll so I
cannot leave the blank rows. (The blank rows are payroll items that
aren't relevant to that employee's current payroll information) The
sorting and copying is all done with a macro if that helps.

One question: How do you reference the employee data (with a simple
cell reference or something else)
Next question: If you only want to remove blank rows, probably
filtering (not sorting) would be the way to do this (goto ' Data -
Filter', choose 'Autofilter' and in the appearing drop down choose 'Non
blanks')

Frank

 

[Greg Liber]

THANKS!
That worked great!

Not to take advantage of your time... but is there a way to automatically
copy only the cells in the sorted range that have the employee number in
column "A" that has been filtered?

AND...if I can impose a little more...
I have a drop down list of all the restaurants which when picked puts a G/L
code into a cell that is used to identify where to charge the monies... the
only difference in the G/L codes is that there is a 3 digit restaurant
account number at the end of the G/L code... however this means a fair
amount of work to set up a new account... is there a way to use the list to
generate the G/L number by using the first part of the G/L code that is
common for all the info and just add the 3 digit account number on the back
in the same cell?

Any help would be greatly appreciated!

Thanks again for your patience and help!

Greg

Hi Greg
[snip]

Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I then sort
to remove all the blank rows, the employee number then becomes 2 from
0002 (if under 999) which isn't too bad because I can format the
columns to 0000 but it sorts with a ton of blank rows in between.
The sheet is then copied onto a new form that I send to payroll so I
cannot leave the blank rows. (The blank rows are payroll items that
aren't relevant to that employee's current payroll information) The
sorting and copying is all done with a macro if that helps.

One question: How do you reference the employee data (with a simple
cell reference or something else)
Next question: If you only want to remove blank rows, probably
filtering (not sorting) would be the way to do this (goto ' Data -
Filter', choose 'Autofilter' and in the appearing drop down choose 'Non
blanks')

Frank

 

[Tom Ogilvy]

For you first question, hard to tell what you mean by automatic, but assume
you mean only the filtered data:
If you copy a filtered range, only the visible cells are copied.

Are you familiar with concatenate

=A1 & b1

=A1 & Left(b1,3)

=A1 & Text(B1,"000")

if B1 contains numbers

perhaps.

--
Regards,
Tom Ogilvy

THANKS!
That worked great!

Not to take advantage of your time... but is there a way to automatically
copy only the cells in the sorted range that have the employee number in
column "A" that has been filtered?

AND...if I can impose a little more...
I have a drop down list of all the restaurants which when picked puts a G/L
code into a cell that is used to identify where to charge the monies... the
only difference in the G/L codes is that there is a 3 digit restaurant
account number at the end of the G/L code... however this means a fair
amount of work to set up a new account... is there a way to use the list to
generate the G/L number by using the first part of the G/L code that is
common for all the info and just add the 3 digit account number on the back
in the same cell?

Any help would be greatly appreciated!

Thanks again for your patience and help!

Greg

Hi Greg
[snip]

Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I then sort
to remove all the blank rows, the employee number then becomes 2 from
0002 (if under 999) which isn't too bad because I can format the
columns to 0000 but it sorts with a ton of blank rows in between.
The sheet is then copied onto a new form that I send to payroll so I
cannot leave the blank rows. (The blank rows are payroll items that
aren't relevant to that employee's current payroll information) The
sorting and copying is all done with a macro if that helps.

One question: How do you reference the employee data (with a simple
cell reference or something else)
Next question: If you only want to remove blank rows, probably
filtering (not sorting) would be the way to do this (goto ' Data -
Filter', choose 'Autofilter' and in the appearing drop down choose 'Non
blanks')

Frank

 

[Frank Kabel]

Hi Greg

Not to take advantage of your time... but is there a way to
automatically copy only the cells in the sorted range that have the
employee number in column "A" that has been filtered?

Not automatically, but using 'Advanced Filters' enables you to copy the
result on a separate worksheet. This may want your are looking for

AND...if I can impose a little more...
I have a drop down list of all the restaurants which when picked puts
a G/L code into a cell that is used to identify where to charge the
monies... the only difference in the G/L codes is that there is a 3
digit restaurant account number at the end of the G/L code... however
this means a fair amount of work to set up a new account... is there
a way to use the list to generate the G/L number by using the first
part of the G/L code that is common for all the info and just add the
3 digit account number on the back in the same cell?

Not sure what you're trying to do. Do you want to automatically change
the result of a cell, filled by a listbox (data validation) after
choosing an entry. Maybe you can give a small example for this

Frank

 

[Greg Liber]

thanks for your help!

Just learned how to use concatenate today! Worked great!

The filtered stuff is a little more difficult... I want to copy the filtered
information from a sheet that the info for the filtered results is a formula
and I need to copy it into a new sheet in a new workbook and I only need the
values in this workbook. (This is what we submit to our payroll department
and they only need the values.) I can copy the info onto another worksheet
using paste special values and then use array and copy the sheet (but this
gives me #ref) or copy the entire section that the info might be stored
(this varies depending on the payroll) but I'm trying to make the file I
transmit as small as I can.

any ideas?

Thanks!
Greg

For you first question, hard to tell what you mean by automatic, but assume
you mean only the filtered data:
If you copy a filtered range, only the visible cells are copied.

Are you familiar with concatenate

=A1 & b1

=A1 & Left(b1,3)

=A1 & Text(B1,"000")

if B1 contains numbers

perhaps.

--
Regards,
Tom Ogilvy

THANKS!
That worked great!

Not to take advantage of your time... but is there a way to automatically
copy only the cells in the sorted range that have the employee number in
column "A" that has been filtered?

AND...if I can impose a little more...
I have a drop down list of all the restaurants which when picked puts a G/L
code into a cell that is used to identify where to charge the monies... the
only difference in the G/L codes is that there is a 3 digit restaurant
account number at the end of the G/L code... however this means a fair
amount of work to set up a new account... is there a way to use the list to
generate the G/L number by using the first part of the G/L code that is
common for all the info and just add the 3 digit account number on the back
in the same cell?

Any help would be greatly appreciated!

Thanks again for your patience and help!

Greg

Hi Greg
[snip]
Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I then sort
to remove all the blank rows, the employee number then becomes 2 from
0002 (if under 999) which isn't too bad because I can format the
columns to 0000 but it sorts with a ton of blank rows in between.
The sheet is then copied onto a new form that I send to payroll so I
cannot leave the blank rows. (The blank rows are payroll items that
aren't relevant to that employee's current payroll information) The
sorting and copying is all done with a macro if that helps.

One question: How do you reference the employee data (with a simple
cell reference or something else)
Next question: If you only want to remove blank rows, probably
filtering (not sorting) would be the way to do this (goto ' Data -
Filter', choose 'Autofilter' and in the appearing drop down choose 'Non
blanks')

Frank

 

[Greg Liber]

Thanks for your help!
learned about concatenate today which seems to work for one of my projects!

The copying and pasting of the filtered info is harder... the result
references the formula for the info whose values I want to copy and I can't
use paste special to paste the data into a new workbook without pasting
somewhere else and then using array to create a new workbook and then copy
the two sheets I need into the new workbook. Any ideas? Is there a way to
copy the info to a temporary page and into the new workbook and control the
name of the sheet?

Your help is always appreciated!

Greg

 

[Tom Ogilvy]

I made a table that contained formulas. I filtered the table, selected the
data (entire row), did edit=>copy

when to another worksheet, selected a single cell in column a, did
Edit=>PasteSpecial and selected values.

Only the visible rows were copied and they were pasted as values.

that sounds like what you want to do.

--
Regards,
Tom Ogilvy

thanks for your help!

Just learned how to use concatenate today! Worked great!

The filtered stuff is a little more difficult... I want to copy the filtered
information from a sheet that the info for the filtered results is a formula
and I need to copy it into a new sheet in a new workbook and I only need the
values in this workbook. (This is what we submit to our payroll department
and they only need the values.) I can copy the info onto another worksheet
using paste special values and then use array and copy the sheet (but this
gives me #ref) or copy the entire section that the info might be stored
(this varies depending on the payroll) but I'm trying to make the file I
transmit as small as I can.

any ideas?

Thanks!
Greg

For you first question, hard to tell what you mean by automatic, but assume
you mean only the filtered data:
If you copy a filtered range, only the visible cells are copied.

Are you familiar with concatenate

=A1 & b1

=A1 & Left(b1,3)

=A1 & Text(B1,"000")

if B1 contains numbers

perhaps.

--
Regards,
Tom Ogilvy

THANKS!
That worked great!

Not to take advantage of your time... but is there a way to automatically
copy only the cells in the sorted range that have the employee number in
column "A" that has been filtered?

AND...if I can impose a little more...
I have a drop down list of all the restaurants which when picked puts

a
G/L

code into a cell that is used to identify where to charge the

monies...
the

only difference in the G/L codes is that there is a 3 digit restaurant
account number at the end of the G/L code... however this means a fair
amount of work to set up a new account... is there a way to use the

list
to

generate the G/L number by using the first part of the G/L code that is
common for all the info and just add the 3 digit account number on the back
in the same cell?

Any help would be greatly appreciated!

Thanks again for your patience and help!

Greg

Hi Greg
[snip]
Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I then sort
to remove all the blank rows, the employee number then becomes 2 from
0002 (if under 999) which isn't too bad because I can format the
columns to 0000 but it sorts with a ton of blank rows in between.
The sheet is then copied onto a new form that I send to payroll so I
cannot leave the blank rows. (The blank rows are payroll items that
aren't relevant to that employee's current payroll information) The
sorting and copying is all done with a macro if that helps.

One question: How do you reference the employee data (with a simple
cell reference or something else)
Next question: If you only want to remove blank rows, probably
filtering (not sorting) would be the way to do this (goto ' Data -
Filter', choose 'Autofilter' and in the appearing drop down choose 'Non
blanks')

Frank

 

[Greg Liber]

Hi!

That works great, but is there a way to write that in a macro? Only copy
the rows with an entry in column "A"? I'm trying to make this as easy for
the users as possible... because when there's a way to mess up the form...
they find it...

Thanks again for all your help!

Greg

I made a table that contained formulas. I filtered the table, selected the
data (entire row), did edit=>copy

when to another worksheet, selected a single cell in column a, did
Edit=>PasteSpecial and selected values.

Only the visible rows were copied and they were pasted as values.

that sounds like what you want to do.

--
Regards,
Tom Ogilvy

thanks for your help!

Just learned how to use concatenate today! Worked great!

The filtered stuff is a little more difficult... I want to copy the filtered
information from a sheet that the info for the filtered results is a formula
and I need to copy it into a new sheet in a new workbook and I only need the
values in this workbook. (This is what we submit to our payroll department
and they only need the values.) I can copy the info onto another worksheet
using paste special values and then use array and copy the sheet (but this
gives me #ref) or copy the entire section that the info might be stored
(this varies depending on the payroll) but I'm trying to make the file I
transmit as small as I can.

any ideas?

Thanks!
Greg

number

in

puts

a

G/L
code into a cell that is used to identify where to charge the monies...
the
only difference in the G/L codes is that there is a 3 digit restaurant
account number at the end of the G/L code... however this means a fair
amount of work to set up a new account... is there a way to use the list
to
generate the G/L number by using the first part of the G/L code that is
common for all the info and just add the 3 digit account number on the
back
in the same cell?

Any help would be greatly appreciated!

Thanks again for your patience and help!

Greg

Hi Greg
[snip]
Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I then sort
to remove all the blank rows, the employee number then becomes 2 from
0002 (if under 999) which isn't too bad because I can format the
columns to 0000 but it sorts with a ton of blank rows in between.
The sheet is then copied onto a new form that I send to payroll

so

I

 

[Tom Ogilvy]

This applies the filter, set the criteria in Column A to "A", copies the
data, removes the autofilter.

Sub CopyData()
Dim rng As Range
Dim rng1 As Range
Set rng = Worksheets("Data")
.Range("A1").CurrentRegion
rng.AutoFilter Field:=1, Criteria1:="A"
Set rng1 = rng.Offset(1, 0). _
Resize(rng.Rows.Count - 1)
rng1.Copy
Sheets("Sheet3").Range("A1") _
.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
rng.AutoFilter
End Sub

change Data to the name of your source sheet and Sheet3 to the name of your
destination Sheet.

--
Regards,
Tom Ogilvy

Hi!

That works great, but is there a way to write that in a macro? Only copy
the rows with an entry in column "A"? I'm trying to make this as easy for
the users as possible... because when there's a way to mess up the form...
they find it...

Thanks again for all your help!

Greg

I made a table that contained formulas. I filtered the table, selected the
data (entire row), did edit=>copy

when to another worksheet, selected a single cell in column a, did
Edit=>PasteSpecial and selected values.

Only the visible rows were copied and they were pasted as values.

that sounds like what you want to do.

--
Regards,
Tom Ogilvy

thanks for your help!

Just learned how to use concatenate today! Worked great!

The filtered stuff is a little more difficult... I want to copy the filtered
information from a sheet that the info for the filtered results is a formula
and I need to copy it into a new sheet in a new workbook and I only

need
the

values in this workbook. (This is what we submit to our payroll department
and they only need the values.) I can copy the info onto another worksheet
using paste special values and then use array and copy the sheet (but this
gives me #ref) or copy the entire section that the info might be stored
(this varies depending on the payroll) but I'm trying to make the file I
transmit as small as I can.

any ideas?

Thanks!
Greg
For you first question, hard to tell what you mean by automatic, but
assume
you mean only the filtered data:
If you copy a filtered range, only the visible cells are copied.

Are you familiar with concatenate

=A1 & b1

=A1 & Left(b1,3)

=A1 & Text(B1,"000")

if B1 contains numbers

perhaps.

--
Regards,
Tom Ogilvy

THANKS!
That worked great!

Not to take advantage of your time... but is there a way to
automatically
copy only the cells in the sorted range that have the employee

number

in
column "A" that has been filtered?

AND...if I can impose a little more...
I have a drop down list of all the restaurants which when picked

puts

a
G/L
code into a cell that is used to identify where to charge the monies...
the
only difference in the G/L codes is that there is a 3 digit restaurant
account number at the end of the G/L code... however this means a fair
amount of work to set up a new account... is there a way to use

the
list

to
generate the G/L number by using the first part of the G/L code

that
is

common for all the info and just add the 3 digit account number on the
back
in the same cell?

Any help would be greatly appreciated!

Thanks again for your patience and help!

Greg

Hi Greg
[snip]
Now the problem is that I can get the formulas to work on the first
worksheet and give me the information that I need. But when I
reference the four digit employee number onto a page that I then
sort
to remove all the blank rows, the employee number then becomes 2
from
0002 (if under 999) which isn't too bad because I can format the
columns to 0000 but it sorts with a ton of blank rows in between.
The sheet is then copied onto a new form that I send to

payroll

so items
that