filtering

I have 400 employees spread across 20 excel worksheets.

When I try to filter by employee name excel does not recognise the text in
cells that are formatted as "general".

Any ideas on resolving this? I have tried changing the relevent firlds to
text but to no avail.

Any ideas?

Thanks

Dave.

I've never had trouble with text in cells formatted as General.

You may want to be more explicit with the problem you're having.

dave glynn wrote:
>
> I have 400 employees spread across 20 excel worksheets.
>
> When I try to filter by employee name excel does not recognise the text in
> cells that are formatted as "general".
>
> Any ideas on resolving this? I have tried changing the relevent firlds to
> text but to no avail.
>
> Any ideas?
>
> Thanks
>
> Dave.
>
>

--

Dave Peterson

Hi Dave

Thanks for your response.

Should have said 20 workbooks, each workbook having 23 worksheets (1 per
employee plus summaries).

Not all employees work every week/

The employee sheet is in effect a database containing all of his personal
details. Each employee is a shareholder of his company and his pay consists
of a combination of wages and dividends worked out weekly and stored in his
worksheet.

The weekly input data comes from another department that uses a different
system but can give me excel output. Initially it is not in a format that
suits me but i runa macro that tidies it up nicely. As not every employee
works every week the out put from the other department is in variable
sequence, i.e: I may have 80 completely different employees to pay this week
as opposed to last. They cannot produce outputs containing zero values
otherwise I could simply map the relevent parts of their database to mine.

I get round this by filtering the outputs so that I can identify each
employee by workbook and worksheet. A series of nested "if statements" then
realign the data so that it can be mapped into the appropriate worksheets.
The nested if statements work, they are in the form:

if(a1="Joe Bloggs", b1,0)+if (a2="Joe Bloggs",b2,0)......b1 and b2 being
values filtered from the other departments outputs.

The text "Joe Bloggs" also comes from the other depatrments outputs and are
filtered into the sheet before the nested ifs take over. HOWEVER the if
statement won't recognise "Joe Bloggs" in its original form. It will only
read it if I overtype it and or add "quotes".

As the purpose of this exercise is, amongst other things, to avoid
repreating data entry I am faced with an equal amount of work and error
potential if I have to overtype any one of 400 possible names.

Thanks again

Kind Regards

Dave.

The double quotes are required. That's the way excel knows you're dealing with
strings and not workbook/worksheet names or even built in functions.

But you may want to look at some other formulas:

=sumif(a1:a10,"joe bloggs",b1:b10)

or even pivottables. These summary tables can give you a nice quick summary
without using many formulas. Although, you'll want your data for the pivottable
on one worksheet.

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

dave glynn wrote:
>
> Hi Dave
>
> Thanks for your response.
>
> Should have said 20 workbooks, each workbook having 23 worksheets (1 per
> employee plus summaries).
>
> Not all employees work every week/
>
> The employee sheet is in effect a database containing all of his personal
> details. Each employee is a shareholder of his company and his pay consists
> of a combination of wages and dividends worked out weekly and stored in his
> worksheet.
>
> The weekly input data comes from another department that uses a different
> system but can give me excel output. Initially it is not in a format that
> suits me but i runa macro that tidies it up nicely. As not every employee
> works every week the out put from the other department is in variable
> sequence, i.e: I may have 80 completely different employees to pay this week
> as opposed to last. They cannot produce outputs containing zero values
> otherwise I could simply map the relevent parts of their database to mine.
>
> I get round this by filtering the outputs so that I can identify each
> employee by workbook and worksheet. A series of nested "if statements" then
> realign the data so that it can be mapped into the appropriate worksheets.
> The nested if statements work, they are in the form:
>
> if(a1="Joe Bloggs", b1,0)+if (a2="Joe Bloggs",b2,0)......b1 and b2 being
> values filtered from the other departments outputs.
>
> The text "Joe Bloggs" also comes from the other depatrments outputs and are
> filtered into the sheet before the nested ifs take over. HOWEVER the if
> statement won't recognise "Joe Bloggs" in its original form. It will only
> read it if I overtype it and or add "quotes".
>
> As the purpose of this exercise is, amongst other things, to avoid
> repreating data entry I am faced with an equal amount of work and error
> potential if I have to overtype any one of 400 possible names.
>
> Thanks again
>
> Kind Regards
>
> Dave.
>
>

--

Dave Peterson

Hi again,


All the formulae do have the ". However when the formula goes to any of the
celld referred to it will not read them unless I first overwrite them; i.e:
unless J Bloggs has quotes round it in cells a1, a2 etc it is not recognised.
Even if I reddefine the cell formats as "text" they are still ignored; only
when I type over a1,a2 et seq are the names recognised,sometimes with quotes,
sometimes without.

Thanks

Dave

"Dave Peterson" wrote:

> The double quotes are required. That's the way excel knows you're dealing with
> strings and not workbook/worksheet names or even built in functions.
>
> But you may want to look at some other formulas:
>
> =sumif(a1:a10,"joe bloggs",b1:b10)
>
> or even pivottables. These summary tables can give you a nice quick summary
> without using many formulas. Although, you'll want your data for the pivottable
> on one worksheet.
>
> If you want to read more about the pivottable stuff, you may want to look at
> some links:
>
> Debra Dalgleish's pictures at Jon Peltier's site:
> http://peltiertech.com/Excel/Pivots/pivottables.htm
> And Debra's own site:
> http://www.contextures.com/xlPivot01.html
>
> John Walkenbach also has some at:
> http://j-walk.com/ss/excel/files/general.htm
> (look for Tony Gwynn's Hit Database)
>
> Chip Pearson keeps Harald Staff's notes at:
> http://www.cpearson.com/excel/pivots.htm
>
> MS has some at (xl2000 and xl2002):
> http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> http://office.microsoft.com/assistan...lconPT101.aspx
>
> dave glynn wrote:
> >
> > Hi Dave
> >
> > Thanks for your response.
> >
> > Should have said 20 workbooks, each workbook having 23 worksheets (1 per
> > employee plus summaries).
> >
> > Not all employees work every week/
> >
> > The employee sheet is in effect a database containing all of his personal
> > details. Each employee is a shareholder of his company and his pay consists
> > of a combination of wages and dividends worked out weekly and stored in his
> > worksheet.
> >
> > The weekly input data comes from another department that uses a different
> > system but can give me excel output. Initially it is not in a format that
> > suits me but i runa macro that tidies it up nicely. As not every employee
> > works every week the out put from the other department is in variable
> > sequence, i.e: I may have 80 completely different employees to pay this week
> > as opposed to last. They cannot produce outputs containing zero values
> > otherwise I could simply map the relevent parts of their database to mine.
> >
> > I get round this by filtering the outputs so that I can identify each
> > employee by workbook and worksheet. A series of nested "if statements" then
> > realign the data so that it can be mapped into the appropriate worksheets.
> > The nested if statements work, they are in the form:
> >
> > if(a1="Joe Bloggs", b1,0)+if (a2="Joe Bloggs",b2,0)......b1 and b2 being
> > values filtered from the other departments outputs.
> >
> > The text "Joe Bloggs" also comes from the other depatrments outputs and are
> > filtered into the sheet before the nested ifs take over. HOWEVER the if
> > statement won't recognise "Joe Bloggs" in its original form. It will only
> > read it if I overtype it and or add "quotes".
> >
> > As the purpose of this exercise is, amongst other things, to avoid
> > repreating data entry I am faced with an equal amount of work and error
> > potential if I have to overtype any one of 400 possible names.
> >
> > Thanks again
> >
> > Kind Regards
> >
> > Dave.
> >
> >
>
> --
>
> Dave Peterson
>

I've never seen excel behave this way.

My guess is that you actually have something else in that cell cell that's
causing the mismatch. And you're fixing that proble when you add the double
quotes. I'm guessing extra leading/trailing/embedded spaces (or non-breaking
spaces (from web sites??)).

You could use Chip Pearson's CellView addin that will show you what each
character is:
http://www.cpearson.com/excel/CellView.htm


And if you add those double quotes to the cell with the names, don't you have to
fix your formulas to include the double quotes?



dave glynn wrote:
>
> Hi again,
>
> All the formulae do have the ". However when the formula goes to any of the
> celld referred to it will not read them unless I first overwrite them; i.e:
> unless J Bloggs has quotes round it in cells a1, a2 etc it is not recognised.
> Even if I reddefine the cell formats as "text" they are still ignored; only
> when I type over a1,a2 et seq are the names recognised,sometimes with quotes,
> sometimes without.
>
> Thanks
>
> Dave
>
> "Dave Peterson" wrote:
>
> > The double quotes are required. That's the way excel knows you're dealing with
> > strings and not workbook/worksheet names or even built in functions.
> >
> > But you may want to look at some other formulas:
> >
> > =sumif(a1:a10,"joe bloggs",b1:b10)
> >
> > or even pivottables. These summary tables can give you a nice quick summary
> > without using many formulas. Although, you'll want your data for the pivottable
> > on one worksheet.
> >
> > If you want to read more about the pivottable stuff, you may want to look at
> > some links:
> >
> > Debra Dalgleish's pictures at Jon Peltier's site:
> > http://peltiertech.com/Excel/Pivots/pivottables.htm
> > And Debra's own site:
> > http://www.contextures.com/xlPivot01.html
> >
> > John Walkenbach also has some at:
> > http://j-walk.com/ss/excel/files/general.htm
> > (look for Tony Gwynn's Hit Database)
> >
> > Chip Pearson keeps Harald Staff's notes at:
> > http://www.cpearson.com/excel/pivots.htm
> >
> > MS has some at (xl2000 and xl2002):
> > http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> > http://office.microsoft.com/assistan...lconPT101.aspx
> >
> > dave glynn wrote:
> > >
> > > Hi Dave
> > >
> > > Thanks for your response.
> > >
> > > Should have said 20 workbooks, each workbook having 23 worksheets (1 per
> > > employee plus summaries).
> > >
> > > Not all employees work every week/
> > >
> > > The employee sheet is in effect a database containing all of his personal
> > > details. Each employee is a shareholder of his company and his pay consists
> > > of a combination of wages and dividends worked out weekly and stored in his
> > > worksheet.
> > >
> > > The weekly input data comes from another department that uses a different
> > > system but can give me excel output. Initially it is not in a format that
> > > suits me but i runa macro that tidies it up nicely. As not every employee
> > > works every week the out put from the other department is in variable
> > > sequence, i.e: I may have 80 completely different employees to pay this week
> > > as opposed to last. They cannot produce outputs containing zero values
> > > otherwise I could simply map the relevent parts of their database to mine.
> > >
> > > I get round this by filtering the outputs so that I can identify each
> > > employee by workbook and worksheet. A series of nested "if statements" then
> > > realign the data so that it can be mapped into the appropriate worksheets.
> > > The nested if statements work, they are in the form:
> > >
> > > if(a1="Joe Bloggs", b1,0)+if (a2="Joe Bloggs",b2,0)......b1 and b2 being
> > > values filtered from the other departments outputs.
> > >
> > > The text "Joe Bloggs" also comes from the other depatrments outputs and are
> > > filtered into the sheet before the nested ifs take over. HOWEVER the if
> > > statement won't recognise "Joe Bloggs" in its original form. It will only
> > > read it if I overtype it and or add "quotes".
> > >
> > > As the purpose of this exercise is, amongst other things, to avoid
> > > repreating data entry I am faced with an equal amount of work and error
> > > potential if I have to overtype any one of 400 possible names.
> > >
> > > Thanks again
> > >
> > > Kind Regards
> > >
> > > Dave.
> > >
> > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi Dave,


Many thanks it worked


Dave

"Dave Peterson" wrote:

> I've never seen excel behave this way.
>
> My guess is that you actually have something else in that cell cell that's
> causing the mismatch. And you're fixing that proble when you add the double
> quotes. I'm guessing extra leading/trailing/embedded spaces (or non-breaking
> spaces (from web sites??)).
>
> You could use Chip Pearson's CellView addin that will show you what each
> character is:
> http://www.cpearson.com/excel/CellView.htm
>
>
> And if you add those double quotes to the cell with the names, don't you have to
> fix your formulas to include the double quotes?
>
>
>
> dave glynn wrote:
> >
> > Hi again,
> >
> > All the formulae do have the ". However when the formula goes to any of the
> > celld referred to it will not read them unless I first overwrite them; i.e:
> > unless J Bloggs has quotes round it in cells a1, a2 etc it is not recognised.
> > Even if I reddefine the cell formats as "text" they are still ignored; only
> > when I type over a1,a2 et seq are the names recognised,sometimes with quotes,
> > sometimes without.
> >
> > Thanks
> >
> > Dave
> >
> > "Dave Peterson" wrote:
> >
> > > The double quotes are required. That's the way excel knows you're dealing with
> > > strings and not workbook/worksheet names or even built in functions.
> > >
> > > But you may want to look at some other formulas:
> > >
> > > =sumif(a1:a10,"joe bloggs",b1:b10)
> > >
> > > or even pivottables. These summary tables can give you a nice quick summary
> > > without using many formulas. Although, you'll want your data for the pivottable
> > > on one worksheet.
> > >
> > > If you want to read more about the pivottable stuff, you may want to look at
> > > some links:
> > >
> > > Debra Dalgleish's pictures at Jon Peltier's site:
> > > http://peltiertech.com/Excel/Pivots/pivottables.htm
> > > And Debra's own site:
> > > http://www.contextures.com/xlPivot01.html
> > >
> > > John Walkenbach also has some at:
> > > http://j-walk.com/ss/excel/files/general.htm
> > > (look for Tony Gwynn's Hit Database)
> > >
> > > Chip Pearson keeps Harald Staff's notes at:
> > > http://www.cpearson.com/excel/pivots.htm
> > >
> > > MS has some at (xl2000 and xl2002):
> > > http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> > > http://office.microsoft.com/assistan...lconPT101.aspx
> > >
> > > dave glynn wrote:
> > > >
> > > > Hi Dave
> > > >
> > > > Thanks for your response.
> > > >
> > > > Should have said 20 workbooks, each workbook having 23 worksheets (1 per
> > > > employee plus summaries).
> > > >
> > > > Not all employees work every week/
> > > >
> > > > The employee sheet is in effect a database containing all of his personal
> > > > details. Each employee is a shareholder of his company and his pay consists
> > > > of a combination of wages and dividends worked out weekly and stored in his
> > > > worksheet.
> > > >
> > > > The weekly input data comes from another department that uses a different
> > > > system but can give me excel output. Initially it is not in a format that
> > > > suits me but i runa macro that tidies it up nicely. As not every employee
> > > > works every week the out put from the other department is in variable
> > > > sequence, i.e: I may have 80 completely different employees to pay this week
> > > > as opposed to last. They cannot produce outputs containing zero values
> > > > otherwise I could simply map the relevent parts of their database to mine.
> > > >
> > > > I get round this by filtering the outputs so that I can identify each
> > > > employee by workbook and worksheet. A series of nested "if statements" then
> > > > realign the data so that it can be mapped into the appropriate worksheets.
> > > > The nested if statements work, they are in the form:
> > > >
> > > > if(a1="Joe Bloggs", b1,0)+if (a2="Joe Bloggs",b2,0)......b1 and b2 being
> > > > values filtered from the other departments outputs.
> > > >
> > > > The text "Joe Bloggs" also comes from the other depatrments outputs and are
> > > > filtered into the sheet before the nested ifs take over. HOWEVER the if
> > > > statement won't recognise "Joe Bloggs" in its original form. It will only
> > > > read it if I overtype it and or add "quotes".
> > > >
> > > > As the purpose of this exercise is, amongst other things, to avoid
> > > > repreating data entry I am faced with an equal amount of work and error
> > > > potential if I have to overtype any one of 400 possible names.
> > > >
> > > > Thanks again
> > > >
> > > > Kind Regards
> > > >
> > > > Dave.
> > > >
> > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>