Help with getting Excel file to clients webpage

[ebackwardse1]

I am really hoping someone can help here. I have a spreadsheet that I set up for a client who is a manufacturer of high end audio products. They have about 100 different products and were using a basic spreadsheet for their distributor and dealer price list.

I made an interactive order form for them with vlookup and data validation that includes price breaks at certain price points and quantity that I have kicking in with formulas. The only cells I want their customers to access are to select the part number and quantity from 1-500 units, which are both done with data validation. I have it working perfectly in Excel for Mac 2011 and PC 2007 and earlier, but I am struggling to find out how to convert it to HTML for their website without losing the interactivity. SkyDrive will not let me open because of the data validation, but this is a must have for my client.

I am desperately trying to find a way (or a company) who can convert this spreadsheet to HTML so my client can embed it on their website.

Please help!

Jeff

[Ken Puls]

Hi ebackwardse1, and welcome to the forum.

I hear you on the data validation not working in the webapp. It's a big miss in my opinion. Having said that, we can put together a workaround using PivotTables which I described in this blog post

Do you think this might work for you?

[ebackwardse1]

Ken,

Thanks so much for your suggestion.

I don't think it will work and here's why. Please feel free to correct me if I am mistaken.

I am using two sheets for my clients order form. The first is the actual order form that has all cells locked except for column A, which contains data validation drop down menus of each of the ~100 products that my client offers, and column B which has a data validation drop down menu with 1-500 so the customer can select the quantity of each product they wish to order. I have 50 lines available for the user to select 50 different products to order with a totals row at the bottom.Columns C-G contain different totals, subtotals, price/unit, etc that are all locked.

The second sheet is hidden and contains a table with the list of my clients product names in column A, and column B, C, D, and E respectively are prices for each product. If the total quantity of all products on the order form is 1-29, the customer gets the price in column B of the table for each product they are ordering. If total quantity is 30-49, they get the price for each product that is shown in column C of the table, and so on. My formula is pretty complicated as you can imagine.

In addition to all of this there are three different volume percentage discounts that kick in at $6k, $10k, and $15k.

I would be happy to email the spreadsheet to you if you want to take a look and see what I am doing.

The other problem I am having is that I am over the hours that I have billed the customer for this project so all the time I spend now is out of my own pocket. That being said, I would like to find a solution for this problem because I am getting a lot of interest from other manufacturers that want me to do something similar for them, and they want to be able to put the order forms on their websites and eliminate having to worry about certain versions of Excel being compatible, etc.

Thanks again for your time and help here!

Jeff

[Ken Puls]

The other problem I am having is that I am over the hours that I have billed the customer for this project so all the time I spend now is out of my own pocket. That being said, I would like to find a solution for this problem because I am getting a lot of interest from other manufacturers that want me to do something similar for them, and they want to be able to put the order forms on their websites and eliminate having to worry about certain versions of Excel being compatible, etc.

I wish I had a silver bullet for this, but I'm afraid that making any workaround for use in SkyDrive is going to take some time... but I still think it CAN be done.

I am using two sheets for my clients order form. The first is the actual order form that has all cells locked except for column A, which contains data validation drop down menus of each of the ~100 products that my client offers, and column B which has a data validation drop down menu with 1-500 so the customer can select the quantity of each product they wish to order. I have 50 lines available for the user to select 50 different products to order with a totals row at the bottom.Columns C-G contain different totals, subtotals, price/unit, etc that are all locked.

Okay, so 50 lines... that's kind of a pain, but honestly, I think it could still work based on that... It's going to be a bit clunky in the client, but in the browser I believe it could be workable.

The second sheet is hidden and contains a table with the list of my clients product names in column A, and column B, C, D, and E respectively are prices for each product. If the total quantity of all products on the order form is 1-29, the customer gets the price in column B of the table for each product they are ordering. If total quantity is 30-49, they get the price for each product that is shown in column C of the table, and so on. My formula is pretty complicated as you can imagine.

Sure, but... it's still possible to make it happen. The kicker is all how you build up the logic.

In addition to all of this there are three different volume percentage discounts that kick in at $6k, $10k, and $15k.

Yeah, just more logic.

So here's the thing... the sticking point is the data validation here. The rest of the formulas and logic (providing you're not using any formulas that aren't supported in the web client) should work fine. It's just a matter of figuring out how to replicated data validation features without using data validation tools.

On the back end, in the client, this could look like a nightmare. But once you hide all those rows down that aren't necessary, and hide the gridlines, the end users will never know... unless they need to download a copy. And if that's the case I might add a separate sheet to summarize the whole thing and protect it.

I would be happy to email the spreadsheet to you if you want to take a look and see what I am doing.

Sure... you can hit me at ken@[the site domain]. I'll warn you that I'm teaching a course Tuesday, and in a course Wednesday, so I may not get to it right away, but I think I can show you how to make this work.