I'm out of ideas.

Cydney wrote:

The Liason (or rep.) is already a row field. What I really need is an empty
field to be part of the PT that let's the user respond yes or no to
"Invite?". That way it would stay with the PT row. But it has to allow the
data entry.
--
THX cs

"Dave Peterson" wrote:

Have you thought of adding another field to the raw data that would indicate the
account rep (or whatever you call them).

Then you could use it as a row field--or even a page field.

Cydney wrote:

Indeed. Which is why we want to use it as a PT because they will be looking
through the client list based on a variety of criteria (liason, region, city,
client name, etc.). I simply need the "yes" response to the invitiation list
to remain with that client's name regardless of what new criteria has been
imposed.

My inexperienced end users won't be converting it to values. We'll be
sending it out to over 100 people to get their responses on who should be
invited. Then compiling that list and modifying our database according to
their changes to names and contact info.

I would consider simply using filters, but even that might require too much
explanation.. PT looks the nicest.. If only we could get this to work.
--
THX cs

"Dave Peterson" wrote:

One of the nicest things about having a pivottable is that it can be
rearranged.

How about creating a pivottable, adding your info, then converting a copy to
values.



Cydney wrote:

I've gotten that to work. And I like the concept --in general-- however, for
my 7000+ records, it takes too long to recalculate with each modification.

Is there another option? Like is there no way I can make the "yes" stay with
the proper client regardless of how the pivot table is swizzled?
--
THX cs

"Dave Peterson" wrote:

Say your pt is on sheet999 and the key values are in A3:Axxx and you want to
plop the formulas in G3:Gxxx

Dim LastRow As Long
Dim myRng As Range
Dim Wks As Worksheet

Set Wks = Worksheets("sheet999")
With Wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set myRng = .Range("G3:G" & LastRow)

myRng.Formula = "=if(isna(vlookup(a3,'sheet 2'!a:b,2,0)),""""," _
& "if(vlookup(a3,'sheet 2'!a:b,2,0)="""",""""," _
& "vlookup(a3,'sheet 2'!a:b,2,0)))"
End With




Cydney wrote:

Hmm. Can you give me an example of how a macro would "reapply" the values?
Or maybe the Vlookup. Those I understand. I'll give that a try.. but I would
also like to entertain the macro option if you'll give me an example.
--
THX cs

"Dave Peterson" wrote:

Maybe you could use a macro to reapply the values--or plop in an =vlookup() to
return the value for each unique key.

Cydney wrote:

That add-in does work.
And it adheres to the conditions of my macro too.

On the same workbook I have a pivot table with client names and addresses
and their in-house liason. I want the users (liasons) to find their group of
clients in the pivot and be able to add a "yes" to columns directly to the
right of the pivot for invitations to the Events that we're going to be
conducting.

My problem is... When a new pivot selection is made, the "Yes's" don't stay
with the right client name.

I'm sure there's a better way.. but I'm just not seeing it.
--
Thanks. cs

"Dave Peterson" wrote:

Maybe you can design your own form and control everything that happens.

John Walkenbach has an enhanced data form:
http://j-walk.com/ss/dataform/index.htm

And the source code is available for a small fee ($20 USA, IIRC).

And if that's not sufficient, then maybe you could design your own input
userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Cydney wrote:

Hi, I'm suggesting that users of a very large spreadsheet use "Data | Forms"
to add or edit data to the sheet. However, for ease of knowing what changes
and additions have been made, I created a macro that highlights the
background color of the cell on the "On change" event of the worksheet. But
it doesn't seem to work when you use the Form. How can I make this happen?
--
THX cs

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson