re: Is there a easy way to use a Excel Pivot table to manage text?

quote:

---

I used to maintain an Excel spreadsheet that was a random collection of pivot tables to make the spreadsheet look like a dashboard, But that was more for my own edification and not something published for others to interact with....

---

This is actually a report I made for me that I was going to try to share with the managers but it is simply too much work for a small payoff. I will just keep copy and pasting the data and send to a managers when needed until I load all the data into ADP and use that platform's custom reports capability which is the long-term solution anyway.

Thanks for all the suggestions

=IF([EXPIRATION DATE CELL]<=TODAY(), “EXPIRED”, “CURRENT”)

I have a Tableau license and would recommend if you’ll be mining complex reports from your data in the future. It’s like a pivot table on fentanyl.

Same brain. Just works alot different.

I don’t even want to know.

Fire Name 8, 12 & 15. NOW!

I would consider indexing the text if that is a possibility, and creating a new column that matches the text index number with the text. Excel handles numbers much better than text and also limits ambiguities bw the texts

quote:

---

I have a large database in an excel spreadsheet

---

No you don’t

quote:

---

I started to use conditional formatting to convert text to a number and then a use a "if # = x, then "text" statement but it seems way to cumbersome to get what I want.

---

quote:

---

---

Without knowing exactly what you are trying to produce as a finished product it’s pretty difficult to tell you exactly how to go about it. But I’ll try to throw out some general suggestions.

First off, working with text in Excel isn’t that difficult if there are no errors in your source data. Error checking is the brutal part of working with text, especially names, as small formatting differences in the data entry can cause issues. This is where unique identifiers (e.g. employee ID numbers) can really make life easier. But it’s not impossible to cross reference names without ID numbers of your data is good.

Second, I suspect almost everything you’re trying to do can be accomplished with the INDEX and MATCH functions. It sounds like you’re using complex strings of IF functions but that’s a really inefficient way to cross reference data. A couple of examples:

If your different certifications have different passing scores and/or expiration periods, create a sheet with the name of the cert, passing score, and how long the cert lasts (preferably in days, since Excel math on dates uses days for addition/subtraction). Then in your main sheet you pull that data from the cert data using INDEX and MATCH (I won’t go into the details of how those functions are constructed, but if you Google index/match you will find plenty of instructions).

So let’s say your input data consists of testing date and score. You create a column that tells you whether the score is passing or not by comparing it to the passing value you entered in the certification data. You can either do that with text (“yes” or “no”), or with numbers (“0” and “1” are the Excel outputs for TRUE/FALSE). Then you conditionally format the score as pass/fail based on that check column.

For expiration date, you do the same cross-reference to add the number of days associated with that particular cert to the testing date.

Same concept applies to filtering by manager. Ideally you have a sheet listing each employee and their manager. Again, unique identifiers (EIN’s) are great here but not necessarily required. You add a “manager” column to your master sheet and cross reference (using INDEX/MATCH) the employee name or EIN with the other sheet to auto-populate the manager name. Now you can easily filter by manager.

Excel isn’t a very good database tool overall but it works for small-ish datasets, which this appears to be.

Why not do equals left or right of the text boxes, shorten the criteria for search and the IF statements

Plenty of ways to do what you’re asking, just depends on how you want to

A manufacturing systems analyst's worst nightmare is unfricking these workbooks when brilliant engineers/business analysts/accountants leave the company. And the next guy/gal has no clue how to make it work. IT HELP USSS!
As smart as you folks are, you're not database developers nor programmers. Give us the raw data or links to your source data and let us deliver it to your dashboard or inbox please.