Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Filtering doesn't work with large files | Excel Discussion (Misc queries) | |||
Combo Box Filtering Macro | Excel Discussion (Misc queries) | |||
avanced filtering for latest date | Excel Discussion (Misc queries) | |||
Filtering | Excel Discussion (Misc queries) | |||
Using Filtering | Excel Discussion (Misc queries) |