Display text in pivot table

I am trying to display text in the value field within a pivot table. I do not want a count or any number to display. What I am basically trying to create is a weekly schedule for nurses, with the dates on the top and times along the side, with "call type" and client as the text data to display with the value field.
I have attached an excel file with all the data on Sheet 1 and my attempt at a pivot table on Sheet 2. Perhaps there is some way to write a macro to display the data in a similar fashion, without using a pivot table?

Clients and Caregivers - All Schedules-2.xls

Hi,
I'm not sure whether It will done by pivot table but I come up with a formula I have attached it.

Originally Posted by Andy Pope

No text in the body of a pivot table.

If you want to retain the flexibility of pivot table selection and filtering then you can nest your row fields.

Thanks Andy! This works great.

Now, I'm wondering if there is a way to automate the process such that any output Sheet 1 will be transferred to a pivot table using macros.
The output data will be generated each week for the nurse schedules, and it would be great to have it automatically format into sheet 2.

Originally Posted by Andy Pope

Please Login or Register  to view this content.

This is amazing. Thank you!

Andy, I was wondering if you (or somebody) could make a few modifications to the code.
In the attachment, Sheet 3 is the output generated by the code above.
- Is there a way to also include "Client" next to call type, as seen in Column B of Sheet 2?
- Insert an empty row between every time slot
- Display all "1"s in the pivot table as white text font
- Display "X" in all blank cells in the pivot table value field

Any additional help would be greatly appreciated.

Originally Posted by Andy Pope

No text in the body of a pivot table.

If you want to retain the flexibility of pivot table selection and filtering then you can nest your row fields.

worked perfectly!

Originally Posted by mmmarks

Hi,
I'm not sure whether It will done by pivot table but I come up with a formula I have attached it.

This is a GREAT table... I very clever way of creating a unique id to solve the order.

QUESTION: how do you make the formula as an array (you know, F2 then ctrl+shift+enter) and still make the cells within the formulas a variables and changing according with the cells.

In other words, within your formula MATCH($G4&H$1,$E$2:$E$620&$D$2:$D$620,0) it changes the G and H values from cell to cell... while everytime I copy the array formula it fixes the G4&H1 as a single value throughout the whole array (and therefore, the formula gets funky)

Can you give me some light here?
thanks!!!

Originally Posted by arlu1201

peponfree,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

No problem Arlu1201... how can I initiate a new question however making reference to the old thread?

Thanks!
:-D