Log In Forums Help
Comic Collector Live
Home :: CCL Messageboard
Find Comics for Sale
Items For Sale
All Comics For Sale
New Releases
CGC Comics
Bundled Lots
Store Locator
Search Library
Search By Title
Publisher
Story Arc
Character
Credits
Release Date
Change Request Manager
News & Reviews
Reviews
News
Our Products and Services
Get the Software
Buying Comics And Stuff
Selling Your Comics
Opening A Store
Community
Forum
Store Locator
Member Locator
Welcome Guest Active Topics
Is anybody here a whiz with Excel formulas, Adobe, &/or Java Script? Options
Xylob
Posted: Tuesday, February 05, 2013 4:22:52 PM

Rank: Beyonder
Groups: Approver, Member, Subscriber

Joined: 8/12/2007
Posts: 8,002
Points: 65,319
I've got a conundrum at work and I need some help.

We've got an Excel spreadsheet that we use as a form for some of our business and it works great for most of what we need, but because many of our users have different versions of Office (don't ask...) the digital signature requirement we have can become bothersome. Furthermore, the way Office handles digital sigs has now been deemed insufficient for our audit requirements.
Wall At Wits' End
PDF files on the other hand... well they handle the digital sigs like a dream.

I've managed to successfully open the Excel file in Adobe Acrobat Pro X and it makes a nice fill-able form that you can tab from field to field in, but ALL of the formula functionality has been stripped. This is no bueno for the majority of the highly educated and extremely intelligent individuals who will be filling the form out on a regular basis - 3rd grade math is beneath (beyond?) them you see.

I've got some formulas that I need to import into this pdf file but it wants scripts - which I don't know squat about.

Here are the formulas:
=IF(I5="G","A",IF(J5="G","A",IF(I5="J","A",IF(J5="J","A",IF(I5="B","A",IF(J5="B","A",IF(I5="H","Q",IF(J5="H","Q",IF(I5="W","Q",IF(J5="W","Q",IF(I5="A","Q",IF(J5="A","Q"))))))))))))
It's long and overly compliated and I know there's got to be a way to simplify the formula but my tinkering has failed so far. If you know how to shorten this without comprising functionality please let me know.
This formula looks at what the user has entered in cells I5 and J5.
If they entered a G, J, or B in those cells, the cell with the formula displays the letter "A".
If they entered an H, W, or A in those cells, the cell with the formula displays a "Q".
What I really need is a translation to the type of script that Adobe requires for a fill-able form.

=IF(I5="X",L5+M5,L5-M5)
This formula looks at what the user has entered in cell I5.
If they entered an X then it adds the amounts in cells L5 and M5.
If they entered anything other than an X it subtracts the amount in cell M5 from the amount in cell L5.

Clear as mud?

If anybody can help I would be greatly appreciative.

TIA!

Need help with a Change Request?Click here to contact an approver!
Problems with CCL?
Send an email right away (don't delay!) to helpdesk@comiccollectorlive.com
615-264-4747
Offices are open M-F 8am-5pm Central Time.
You can also e-mail the Chief Brand Officer directly to try to resolve questions/stuff at steve@golocomedia.com for help with password resets, general customer service questions, store order resolution, credit card store updating, questions about comic books and CCL, etc...

Xylob's Most Wanted:

G.I.Joe Special Missions TPB 2
Tales from the Transformers Beast Wars: Critical Mass
Youngblood Bloodsport #2 maybe? can you help identify?
MoonKnight1
Posted: Tuesday, February 05, 2013 4:30:12 PM

Rank: Celestial
Groups: Guru, Member, Moderator, Sports Host, Super Seller

Shop at My Store

Joined: 5/24/2010
Posts: 2,984
Points: 10,961
Location: Richmond, VA
You lost me at "conundrum" ...

Fantastic News! Hall of Heroes has now surpassed 600 Hero Ratings! I couldn't have done it without all of you and I will strive to maintain and improve my high standards. Which brings me to my next announcement - starting now, all shipments will come with tracking at no extra charge to you! I'm a little late to the party but as they say - better late than never. You spoke and I heard you loud and clear. Excelsior!



Now with 1,500+ positive feedback on the four letter site!
Standing offer to all CCL Members - every purchase you make from my other store (link below) earns you at least one freebie of my choice! If you purchase from Hall Liquidations send a PM to Hall of Heroes and let me know! Thanks!

Hall Liquidations


comicscastle
Posted: Tuesday, February 05, 2013 7:05:09 PM

Rank: Beyonder
Groups: Approver, Approver Steward, CR-Management, Forum Admin, Grade My Book Host, Guru, History Host, Member, Super Seller, Tool

Shop at My Store

Joined: 1/30/2008
Posts: 15,002
Points: 425,892
Location: New Jersey
You don't really need help, you're just showing off.



The following stores are all stores that I've dealt with or have become friends with through the forums and I highly recommend them all.
Comics Castle-owned & operated by Pat McCauslin
Alpha Comics--ComicVortex--Metropolis Connections 2.0--Comic Cellar


komohah
Posted: Tuesday, February 05, 2013 7:27:24 PM

Rank: Watcher
Groups: Member, Subscriber

Joined: 7/5/2009
Posts: 592
Points: 276,540
Location: New Zealand
Xylob wrote:


Here are the formulas:
=IF(I5="G","A",IF(J5="G","A",IF(I5="J","A",IF(J5="J","A",IF(I5="B","A",IF(J5="B","A",IF(I5="H","Q",IF(J5="H","Q",IF(I5="W","Q",IF(J5="W","Q",IF(I5="A","Q",IF(J5="A","Q"))))))))))))
It's long and overly compliated and I know there's got to be a way to simplify the formula but my tinkering has failed so far. If you know how to shorten this without comprising functionality please let me know.
This formula looks at what the user has entered in cells I5 and J5.
If they entered a G, J, or B in those cells, the cell with the formula displays the letter "A".
If they entered an H, W, or A in those cells, the cell with the formula displays a "Q".


I'm afraid that I can't help with th Adobe script but the formula =IF(OR(I5="G",I5="J",I5="B",J5="G",J5="J",J5="B"),"A","Q") will give you the exact same results as your formula above (based on all of the possible combinations of G, J, B, H, W & A in cells I5 & J5). If the values entered in I5 & J5 are always identical then the formula could be shortened to just =IF(OR(I5="G",I5="J",I5="B"),"A","Q") but I'm assuming the cells could contain any of the following combinations:
G & G
G & J
G & B
G & H
G & W
G & A
J & G
J & J
J & B
J & H
J & W
J & A
B & G
B & J
B & B
B & H
B & W
B & A
H & G
H & J
H & B
H & H
H & W
H & A
W & G
W & J
W & B
W & H
W & W
W & A
A & G
A & J
A & B
A & H
A & W
A & A
Xylob
Posted: Tuesday, February 05, 2013 9:10:28 PM

Rank: Beyonder
Groups: Approver, Member, Subscriber

Joined: 8/12/2007
Posts: 8,002
Points: 65,319
actually...
the only acceptable combinations for cells I5 and J5 are:
XG
XH
XB
XA
XJ
XW
GX
HX
BX
AX
JX
WX
anything else would get sent back to the submitter...
Probably should have included that info from the get go.
Q isn't an option for user input - it's just one of the possible results in the cell containing the formula.
confusing the matter more?
Silly

Need help with a Change Request?Click here to contact an approver!
Problems with CCL?
Send an email right away (don't delay!) to helpdesk@comiccollectorlive.com
615-264-4747
Offices are open M-F 8am-5pm Central Time.
You can also e-mail the Chief Brand Officer directly to try to resolve questions/stuff at steve@golocomedia.com for help with password resets, general customer service questions, store order resolution, credit card store updating, questions about comic books and CCL, etc...

Xylob's Most Wanted:

G.I.Joe Special Missions TPB 2
Tales from the Transformers Beast Wars: Critical Mass
Youngblood Bloodsport #2 maybe? can you help identify?
Xylob
Posted: Tuesday, February 05, 2013 9:17:02 PM

Rank: Beyonder
Groups: Approver, Member, Subscriber

Joined: 8/12/2007
Posts: 8,002
Points: 65,319
oh yeah, one other thing: Thanks Brett, I'll plug it in tomorrow and give her a whirl!

Need help with a Change Request?Click here to contact an approver!
Problems with CCL?
Send an email right away (don't delay!) to helpdesk@comiccollectorlive.com
615-264-4747
Offices are open M-F 8am-5pm Central Time.
You can also e-mail the Chief Brand Officer directly to try to resolve questions/stuff at steve@golocomedia.com for help with password resets, general customer service questions, store order resolution, credit card store updating, questions about comic books and CCL, etc...

Xylob's Most Wanted:

G.I.Joe Special Missions TPB 2
Tales from the Transformers Beast Wars: Critical Mass
Youngblood Bloodsport #2 maybe? can you help identify?
komohah
Posted: Wednesday, February 06, 2013 2:52:20 AM

Rank: Watcher
Groups: Member, Subscriber

Joined: 7/5/2009
Posts: 592
Points: 276,540
Location: New Zealand
Xylob wrote:
actually...
the only acceptable combinations for cells I5 and J5 are:
XG
XH
XB
XA
XJ
XW
GX
HX
BX
AX
JX
WX


Ah, that makes more sense Big Grin

The formula I gave earlier:
=IF(OR(I5="G",I5="J",I5="B",J5="G",J5="J",J5="B"),"A","Q")
will work as long as it's only possible to enter one of the acceptable combinations. If it's possible to enter anything else then this formula will give a result of "Q". If the possible entries acceptable in cells I5 & J5 are locked in by way of a validation list, then I believe the only deviation possible from the acceptable combinations would be XX (in which case my formula above would give the result of "Q").

This formula:
=IF(OR(I5="G",I5="J",I5="B",J5="G",J5="J",J5="B"),"A",IF(AND(I5="X",J5="X"),"FALSE","Q"))
will return the same results as your existing formula in the case of validation being used and takes into account the possibility of an XX entry.

This formula:
=IF(OR(I5="G",I5="J",I5="B",J5="G",J5="J",J5="B"),"A",IF(OR(I5="H",I5="W",I5="A",J5="H",J5="W",J5="A"),"Q"))
will return the exact same results as your existing formula regardless of whether validation is being used or not.

Tongue
Xylob
Posted: Wednesday, February 06, 2013 6:45:42 AM

Rank: Beyonder
Groups: Approver, Member, Subscriber

Joined: 8/12/2007
Posts: 8,002
Points: 65,319
Hypnotized
I will take your word for it.

Need help with a Change Request?Click here to contact an approver!
Problems with CCL?
Send an email right away (don't delay!) to helpdesk@comiccollectorlive.com
615-264-4747
Offices are open M-F 8am-5pm Central Time.
You can also e-mail the Chief Brand Officer directly to try to resolve questions/stuff at steve@golocomedia.com for help with password resets, general customer service questions, store order resolution, credit card store updating, questions about comic books and CCL, etc...

Xylob's Most Wanted:

G.I.Joe Special Missions TPB 2
Tales from the Transformers Beast Wars: Critical Mass
Youngblood Bloodsport #2 maybe? can you help identify?
yourplace2
Posted: Thursday, February 07, 2013 8:05:24 AM

Rank: Large Noggin
Groups: Member, Super Seller

Shop at My Store

Joined: 4/16/2008
Posts: 392
Points: 2,270
You guys really need to read more comics.

Rolling on the Floor Dancing

Join the store on FACEBOOK!



* All items must be returned prior to refund at your expense. Return shipping not refunded.



Users browsing this topic
Guest


Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Main Forum RSS : RSS

This page was generated in 0.173 seconds.

ADVERTISEMENT . . . . . . . . . . . . . . . . . . . . . . . .
All images on comic collector live copyright of their respective publishers. © Copyright 2008, MidTen Media Inc. GOLO231