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