VSP signature and choice spreadsheet (excel)

[chaoticneutral]

Good evening Optiboard goers,

In an interest to help my office go paperless ive spent countless hours learning how to and building an excel spreadsheet for VSP Signature and Choice that works beautifully. My boss, although interested in the project, does not seem over enthusiastic about implementing it into the current practice. As it stands, ive put a frame calculater to tabulate the frame cost using the WFA allowance, and the pt frame allotment, Ive included 200 lens choices broken down into basic lens Cost and Fee, Material cost and fee, coating cost and fee......etc etc its all there and I used drop down menus and Vlookup formulas to ensure its accuracy. Has anyone else put anything like this together? If your interested in taking a look ill be happy to share it with you. I'm quite proud of it and want to see this used by those that might appreciate the effort (damn you foolish pride). Send me a message and ill email you the sheets, Ill take out our pricing because i don't think it would be fair, or ethical to include what we charge versus what you might charge. We all have to get along and make our own way. Ive been in enough offices to know that everyone likes to charge whatever they think is .... fair? honest? profitable? I dunno, you can pick your own adjectives.

Have a lovely evening

[Eyedentity3]

I would love to see what you have come up with. I made a similar type of spreadsheet awhile back comparing lab costs and a version that autofilled a price sheet based off of a desired margin per category that included VSP pricing. It is about 20 sheets overall, and was a lot of fun actually.

js

[Uncle Fester]

Cool!

Welcome to the posting side of Optiboard!

And what an initial post!!

I'm off until Monday but I look forward to following what others have to say.

[Uilleann]

I was of the understanding that the special lens allowance was going away soon in its entirety.

Confirm/deny??

[DanLiv]

I was of the understanding that the special lens allowance was going away soon in its entirety.

Confirm/deny??

Already gone except for specifically approved "brand" rx lenses, e.g. Oakley, Maui Jim, Ray-Ban, etc.

VSP to discontinue special lens allowance

http://www.optiboard.com/forums/show...lens-allowance

[jakef]

I think that is a valuable tool to have. Good job on tackling that, it isn't easy. I thought of a similar setup, except in HTML so you could have drop down selections to go through the order process, but never got too far into it (our EMR now has VSP integrated so it does it for us anyway).

[chaoticneutral]

Hey there Optiboard,

Here are the spreadsheets that I came up with.
There are a few things you should know about them.
First, ive found they don't work with all versions of Excel. I built it on whatever version my office has. I think its 2007? im not exactly sure. That being said, all of the functionality may not cross over when you try to open it on whatever version of excel you are using. I tried to open it on my computer at home for instance, and the drop down menus had to be reformulated, but those are very easy to fix and if you need a walk through its super easy to do.

If any of the functions don't work most of them are very easy to fix.

The biggest problem might be the macros that clear the sheet. Macros and the raido buttons are a little more complicated to fix.

SO, if you click BASE LENS (purple cell) there should be a drop down menu. If there isn't that needs to be fixed.
If you click on AR (orange lens) there should be a drop down menu there as well. If there isnt then that needs to be fixed.
CELLS K-H may or may not be hidden. These cells are formulated with the Vlookup function to pull data from specific cells depending on what lens you choose from the drop down menu, they are then linked to whatever cell they belong in, and summed up at the bottom.

Are you some kind of wizard? No, sorry just some sucker that got tricked into watching hours of you tube video by his boss with promise of praise and cake and then ... WHAT? NO CAKE? Damn you cruel fate!!

What the hell is this Optos thing with a button that costs 39$? well, that's real you can take that out if you like. just something my office uses.

Disclaimer:

The rest of the pricing has been altered to protect the innocent. All comments are mine mine mine!! However, they may be borrowed free-of-charge at your own risk for up to 2 weeks. $0.25/day late fee. All generic disclaimers apply. All opinions are my wife's; I have none. (In my opinion.) All rights reserved - but some wrongs are still available. All the stupid has been said, there is nothing I can add. And on the Eighth day, the Lord made a backup copy. Any disclaimer issued by me is subject to change without notice. Any resemblance between the above views and those of my employer, my terminal, or the view out my window are purely coincidental. Any similarity to actual opinions, living or dead, is purely coincidental. Any spelling mistakes in this post are all entirly my fault. Any grammer errors spotted in this article were put there because I could. Apply smileys liberally throughout the above as needed. If that doesn't help, take 2 valium and call your analyst in the morning. Calm down -- it's only ones and zeros. Did I say that ? Please Don't abuse my opinions, I'm lucky to have them.

[chaoticneutral]

Hey there Optiboard,

Here are the spreadsheets that I came up with.
There are a few things you should know about them.
First, ive found they don't work with all versions of Excel. I built it on whatever version my office has. I think its 2007? im not exactly sure. That being said, all of the functionality may not cross over when you try to open it on whatever version of excel you are using. I tried to open it on my computer at home for instance, and the drop down menus had to be reformulated, but those are very easy to fix and if you need a walk through its super easy to do.

If any of the functions don't work most of them are very easy to fix.

The biggest problem might be the macros that clear the sheet. Macros and the raido buttons are a little more complicated to fix.

SO, if you click BASE LENS (purple cell) there should be a drop down menu. If there isn't that needs to be fixed.
If you click on AR (orange lens) there should be a drop down menu there as well. If there isnt then that needs to be fixed.
CELLS K-H may or may not be hidden. These cells are formulated with the Vlookup function to pull data from specific cells depending on what lens you choose from the drop down menu, they are then linked to whatever cell they belong in, and summed up at the bottom.

Are you some kind of wizard? No, sorry just some sucker that got tricked into watching hours of you tube video by his boss with promise of praise and cake and then ... WHAT? NO CAKE? Damn you cruel fate!!

What the hell is this Optos thing with a button that costs 39$? well, that's real you can take that out if you like. just something my office uses.

Disclaimer:

The rest of the pricing has been altered to protect the innocent. All comments are mine mine mine!! However, they may be borrowed free-of-charge at your own risk for up to 2 weeks. $0.25/day late fee. All generic disclaimers apply. All opinions are my wife's; I have none. (In my opinion.) All rights reserved - but some wrongs are still available. All the stupid has been said, there is nothing I can add. And on the Eighth day, the Lord made a backup copy. Any disclaimer issued by me is subject to change without notice. Any resemblance between the above views and those of my employer, my terminal, or the view out my window are purely coincidental. Any similarity to actual opinions, living or dead, is purely coincidental. Any spelling mistakes in this post are all entirly my fault. Any grammer errors spotted in this article were put there because I could. Apply smileys liberally throughout the above as needed. If that doesn't help, take 2 valium and call your analyst in the morning. Calm down -- it's only ones and zeros. Did I say that ? Please Don't abuse my opinions, I'm lucky to have them.

Oh, and if you do take a look at the second sheet with all the pricing, you may notice that there are blocks of pink, thoese were the specialty lense pricing which I had created formulas for but then, almost the next day, vsp nixed them. We are currently using a blanket 20% off the lenses so you might notice the formula in there for =*.80 or whatever.

yay!

[EyeCare Rich]

I'll have to check out your spreadsheets chaoticneutral, but I absolutly love your disclaimer. Nice to see another sence of humor on the board. I look forward to more posts and responses from you!

As Fezz would say, Cheers!

And here is my application of smiley face!

[chaoticneutral]

Ok optiboard goers I need some help -
We use Officemate in our optical. It's fine and I know there are those that like it and those that don't. Sadly, it's what we have in place. Iv'e contacted them and they do not support Excel or Office in anyway so at the moment there is no way to link this spread sheet to Officemate......which is exactly what I want to do. There must be some way to map AT LEAST the lens choice to the excel spreadsheet either by exporting or by third party software. When I choose the lens in Office mate I want it selected automatically on the excel side to. Any thoughts? Also, are there any changes to the current sheets that would make them better? Has anyone toyed around with them?

Lemme know =)

[Uilleann]

Ok optiboard goers I need some help -
We use Officemate in our optical. It's fine and I know there are those that like it and those that don't. Sadly, it's what we have in place. Iv'e contacted them and they do not support Excel or Office in anyway so at the moment there is no way to link this spread sheet to Officemate......which is exactly what I want to do. There must be some way to map AT LEAST the lens choice to the excel spreadsheet either by exporting or by third party software. When I choose the lens in Office mate I want it selected automatically on the excel side to. Any thoughts? Also, are there any changes to the current sheets that would make them better? Has anyone toyed around with them?

Lemme know =)

What version of OM are you using? If you're in anything 10.0+, you are likely screwed (if you'll pardon the parlance, it's the only word that truly fits) - as they say. I've found their integration support to be appallingly poor for the "new and improved" version. There is some you can do with the lens attributes and pricing - but it is a rat's nest in there. You're probably safer hacking your computer's own registry than messing with that computer programmer's Jenga game! :)

[chaoticneutral]

What version of OM are you using? If you're in anything 10.0+, you are likely screwed (if you'll pardon the parlance, it's the only word that truly fits) - as they say. I've found their integration support to be appallingly poor for the "new and improved" version. There is some you can do with the lens attributes and pricing - but it is a rat's nest in there. You're probably safer hacking your computer's own registry than messing with that computer programmer's Jenga game! :)

I do love to put on my grey hat and tinker. Were using 10.0.8 or something. Im resigned to finding some kind of third party software that will do the job for me. Thankyou for getting back to me tho, Im hoping to get some more feed back and see how I can streamline using these sheets. Im building more for eyemed and some of the other insurances we take. Its fun, but a lot of work.

[Jubilee]

It supposedly has the ability to integrate with quickbooks. Quickbooks does have a reports feature that would allow you to export that data into a spreadsheet form. You could use that report as the dataset for the spreadsheet you created.

OM-> Quickbooks -> Excel

If you have access to the Officemate database, you might be able create a query to pull the information into your spreadsheet. At least that is how I imagine other 3rd party solutions are working it.

The Lens Attribute Pricing was a simple set up for us. Then again, we were new to OM in version 10.6/7 and had streamlined a fair amount of pricing allowing for the "component" pricing set up. If you need some assistance, let me know.

I am not thrilled with the VSP integration either. Heaven forbid if you need to use a backdated/expired auth. It gets even more complex for us since some of Indiana's Medicaid is managed by VSP, but has different policies. Find myself usually filing the medicaid through Eyefinity to prevent one long series of issues. For VSP and Choice, the autocalc feature seems to work in most cases. The times we see it not work are usually when it involves digital SV.

[chaoticneutral]

so much for easy....or convenient....Thanks for the input Jubilee.

[chaoticneutral]

any feedback on the sheets? questions? comments? concerns?

[chaoticneutral]

Anyone? Bueller......Bueller