Re: Data Forms

[Dave Peterson]

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

[Cydney]

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]

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

--

Dave Peterson

[Cydney]

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]

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

--

Dave Peterson

[Cydney]

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?

[Dave Peterson]

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.

--

Dave Peterson

[Cydney]

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.

[Dave Peterson]

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.

--

Dave Peterson

[Cydney]

Is it possible to "embed" this add-in so people have it pre-installed when I
distribute the Excel workbook?
--
THX cs

[Cydney]

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.

[Dave Peterson]

I'm out of ideas.

--

Dave Peterson

[Dave Peterson]

From John's site:

The Enhanced Data Form is free, and is not crippled in any way. It is provided
with no strings attached, no nag messages, and no ads. It can be freely
distributed and used without a license. However, it may not be sold, or included
as part of any other product without the written permission of J-Walk &
Associates.

===
If you bought the source, you could include it in your workbook's project. If
you don't, it looks like you'd have to use separate files.

--

Dave Peterson

[piper]

Dave, I have used your example of the worksheet data entry formand just
discovered I cannot use it on my IPAQ, can you advise me if it is possible to
do something similar for a pocket PC ie (Pocket Excel). Your help would be
really very much appreciated, if there is anyway possible I would be most
grateful.

Yours Aye

Bruce

[Dave Peterson]

Sorry, I can't help.

I've never even seen a pocket pc or used pocket excel.

Maybe someone else will chime in or you could find a forum dedicated to those???

--

Dave Peterson

[piper]

Ok thanks anyway, just need to use my laptop!

PS your form is great

B