Excel Help Needed -Add multiple IF statements to a combined string of cells

By little_dobby ·
<P>I cannot figure out the best way to make this formula as simple as possible so that I don't confuse myself!  I have an ecommerce database I'm trying to build in Excel for our website.  I have never made a database-driven website before.  I'm using Excel 2007.  I have no idea how to make a macro, so I'm using a formula.  I need to have the text from multiple cells combined together into one cell (I'm using the A3&"-"&A4 type of formula), because I want to have one cell that I can reference from the database to input the product information for a given product. </P>
<P>The columns have data that I want turned into bullets after the main product description.  But if there isn't anything listed in the cell (cell ISBLANK), then I don't want anything to show up for that cell, and I want it to go onto showing the info. for the next cell and so on.  </P>
<P>Here are the Columns I'm using for this formula:</P>
<P>AP = Item Description<BR>AQ = Item Info. Bullet #1<BR>AR = Item Info. Bullet #2<BR>AS = Item Info. Bullet #3<BR>AT = Item Info. Bullet #4<BR>AU = Item Info. Bullet #5<BR>AW = Warranty (i.e. 1 year or 6 months, etc.)<BR>AX = Warranty Language (i.e. This is a limited warranty...)<BR>AZ = UL or ETL Listed<BR>BA = Wattage</P>
<P>For some items, collumn AW for Warranty will say "1 year," while for other items this cell is simply blank.  Same thing for whether or not the wattage is listed for that product or if it says it is UL listed or just doesn't say.</P>
<P>I can't figure out how to paste my Excel formula without the site taking the html and converting it to html instead of text only.  So it makes it hard to follow my Excel formula.  If you know how I can even post my formula here in text instead of html, that would be helpful, too!  Thank you!</P>
<P>Here is my formula so far (the simplified version):</P>
<P>=CLEAN($AP2&"<ul><b><font face=Arial size=2> <li>"&IF(ISBLANK($AQ2),"",$AQ2&"</li><li>"&$AR2&"</li><li>"&$AS2&"</li><li>"&$AT2&"</li><li>"&$AU2&"</li><li>"&"UL or ETL Listed?  "&$AZ2&"</li><li>"&"Watts:  "&$BA2&"</li><li>"&"Warranty:  "&$AW2&"</li><li>"&"Warranty Language:<br><br>"&$AX2&"</li></font></b></ul>"))</P>
<P>If I keep it like it is and there isn't any data in cell $AQ2 (Item info. bullet #1), then it doesn't just skip this cell and go onto putting in the info. for the next cell, etc.  It just stops there.</P>
<P>I've also tried the version below, but When I copy & paste this cell info. into my HTML program, I see it is making a "blank" bullet for each cell, even for cells that don't have any data instead of just skipping those cells:</P>
<P>=CLEAN(IF($AU2="",$AP2&"<ul><b><font face=Arial size=2> <li>"&$AQ2&"</li><li>"&$AR2&"</li><li>"&$AS2&"</li><li>"&$AT2&"</li><li>"&"UL or ETL Listed?  "&$AZ2&"</li><li>"&"Watts:  "&$BA2&"</li><li>"&"Warranty:  "&$AW2&"</li><li>"&"Warranty Language:<br><br>"&$AX2&"</li></font></b></ul>",IF($AW2="",$AP2&"<ul><b><font face=Arial size=2> <li>"&$AQ2&"</li><li>"&$AR2&"</li><li>"&$AS2&"</li><li>"&$AT2&"</li><li>"&"UL or ETL Listed?  "&$AZ2&"</li><li>"&"Watts:  "&$BA2&"</li><li>"&"Warranty Language:<br><br>"&$AX2&"</li></font></b></ul>",IF($AX2="",$AP2&"<ul><b><font face=Arial size=2> <li>"&$AQ2&"</li><li>"&$AR2&"</li><li>"&$AS2&"</li><li>"&$AT2&"</li><li>"&"UL or ETL Listed?  "&$AZ2&"</li><li>"&"Watts:  "&$BA2&"</li><li>"&"Warranty:  "&$AW2&"</li></font></b></ul>",IF($AZ2="",$AP2&"<ul><b><font face=Arial size=2> <li>"&$AQ2&"</li><li>"&$AR2&"</li><li>"&$AS2&"</li><li>"&$AT2&"</li><li>"&$AU2&"</li><li>"&"Watts:  "&$BA2&"</li><li>"&"Warranty:  "&$AW2&"</li><li>"&"Warranty Language:<br><br>"&$AX2&"</li></font></b></ul>",IF($BA2="",$AP2&"<ul><b><font face=Arial size=2> <li>"&$AQ2&"</li><li>"&$AR2&"</li><li>"&$AS2&"</li><li>"&$AT2&"</li><li>"&$AU2&"</li><li>"&"UL or ETL Listed?  "&$AZ2&"</li><li>"&"Warranty:  "&$AW2&"</li><li>"&"Warranty Language:<br><br>"&$AX2&"</li></font></b></ul>",$AP2&"<ul><b><font face=Arial size=2> <li>"&$AQ2&"</li><li>"&$AR2&"</li><li>"&$AS2&"</li><li>"&$AT2&"</li><li>"&$AU2&"</li><li>"&"UL or ETL Listed?  "&$AZ2&"</li><li>"&"Watts:  "&$BA2&"</li><li>"&"Warranty:  "&$AW2&"</li><li>"&"Warranty Language:<br><br>"&$AX2&"</li></font></b></ul>"))))))</P>
<P>I'd like to be able to use the "ISBLANK" thing or something like it to put an argument for each cell, so that if there isn't anything listed in a cell for "Wattage," then I can make it also not include this part of the formula:  &"Watts:  "</P>
<P>I have spent a very long time on this and I cannot figure it out.  Please help!!!  Thank you so much in advance!</P>

This conversation is currently closed to new comments.

17 total posts (Page 2 of 2)   Prev   01 | 02
Thread display: Collapse - | Expand +

All Answers

Collapse -

If that's what you want to do eventually....

by ThumbsUp2 In reply to Thanks

Why bother with concatenating data into one cell in the spreadsheet? You're going about things completely backwards. You're eventually going to have to import that data into some sort of database online which will not allow the formulas in the data, then use a programming language to extract the data to be displayed on the web page. By using a language such as PHP, you can extract the data from the real database (not Excel), check to see if the field is blank and make the decision to show a bulleted item or not on the web page based on the contents of each cell.

Why try to test in FrontPage if you know it's not going to do what you want it to do and won't be what you're going to use for the end result?

Just get the data ready in Excel and nothing more. There shouldn't be any kind of formulas in that data. It should be straight data only. Then, start learning how to use the tools that your server (host) will recognize. For example, some servers will provide MySQL for a database and will allow PHP programming. Other servers use SQL and .ASP. FrontPage is only used to manage the files within the "web" and keep both the local copy and the online copy synchronized. It's not used to do the actual programming.

Collapse -

I never answered your question about why I want to bother with this...

by little_dobby In reply to If that's what you want t ...

I don't know how long it will take to get a dynamic database-driven site up and running. In the meantime, we want to be able to start selling these 500-700 items from this one vendor on various online selling sites. For that, I needed an item description for one cell, as most selling sites have just one cell of a csv file that you can input info. for the product description. Sorry - I actually had been messing with that formula for so long that I forgot why I needed it!

Our current web host only has PHP programming, but I'm not set on using them for sure for our updated site.

Also, to get all the data from our current site (we currently have nearly 800 different product pages that we built manually using FrontPage 2003), I need to extract the product title, description, bullets (some products have 15-20 bullets of info.), image URLs, and if possible, the metatag data I've already made. I would then need to input this into this same Excel spreadsheet database I'm building. Do you know of a way to automate pulling data from our current site's HTML and putting it into an Excel or csv or txt file. There is a lot of info. in the HTML files I don't need (tables info., includes pages, etc.).

The only way I know how to do this is to go into FrontPage for every single product page we already have made and copy / paste the product description into a cell in Excel and then copy / paste the html code for the product's bulleted list into the next cell of that row, and do the same for the images and the metatag data. With nearly 800 htm pages, this will take forever to do it manually. Does anyone know of a way I can automate some or all of this giant task?? I don't know if I'm allowed to include the URL to our site here or not, so I have left it out. If you need it for reference in helping me figure this out, please let me know. Any info. at all will be greatly appreciated. Thank you!


Collapse -

re: extracting data from web page to Excel....

by ThumbsUp2 In reply to I never answered your que ...

There is no easy way to do it. As far as I know, without your current web site being run out of a database, you're going to have to do it one page at a time, one item at a time.

However, I might be able to help with HOW you get each item out of the site and over to Excel. You can copy the item data itself out of viewing the page in a browser. Don't do it out of FrontPage. That copies too much "crap". Paste what you've copied directly into Excel. It's better if the item data is in a table on the web page, but it works (to a point) without it. You might have a little cut-n-pasting to do, depending on how it's formatted on the page. But it can be done.

It's at this point that everything gets really tricky though. What we've done is to write custom macro's (VB) to extract the contents of that pasted data (we call it raw data which has the formatting still in it, colors and all) into another sheet in the same workbook. The data is then clean and in a format that we can export to CSV and import to a MySQL database online. There, I have PHP programming to extract the data from MySQL for use in the pages generated by more PHP programming. The PHP is written using a text editor, but the PHP files are all stored in my FrontPage "web" so I can manage them easily (upload/synchronize). I go through this process at least once a month.

PHP isn't all that hard to learn. If you can learn the syntax of that formula you ended up with (Whew We!!!), you can learn the syntax of a programming language. All you need to add is the logic to figure out the flow of the program. Macros (VB) aren't all that hard either. You can do it, so go for it!

Collapse -

I've got my formula working perfectly now...

by little_dobby In reply to What exactly are you tryi ...

Thank you very much for your help.

Collapse -

Glad you got it working...

by ThumbsUp2 In reply to I've got my formula worki ...

I tried it that way and couldn't get past the syntax checker. Probably had a quote or three out of place. There's a lot of quotin' goin' on there. LOL!!!

Anyway, glad you've got it.

Collapse -

Thank you again

by little_dobby In reply to Glad you got it working.. ...

I couldn't reply to the last part you wrote about PHP and VB, etc. (it said I had reached the maximum level of replies to that post). The only real reason I wanted to use FrontPage to copy/paste the data was to retain the bulleted list html code (paste it into the formula bar of Excel, not into the cell). I'll look into what you're talking about as far as copy/pasting as-is from our site into one Excel worksheet and then pulling it into another worksheet in that same workbook. Thank you again for your help, and for the vote of confidence!! :) Take it easy!


Collapse -

re: Reached maximum.....

by ThumbsUp2 In reply to Thank you again

When a thread has reached it's maximum, just reply to the post above it (up one level) and put in the title who you're replying to (to save confusion).

We all deal with the limit on posts, practically on a daily basis. It's confusing at first to a new comer. But, you get used to it after a while.


Back to Software Forum
17 total posts (Page 2 of 2)   Prev   01 | 02

Related Discussions

Related Forums