Question

Locked

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.

18 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

???????

by ThumbsUp2 In reply to Excel Help Needed -Add mu ...

I'm looking at the first formula you listed. The simplified version. What cell are you trying to put this formula into?

edited to add: After rereading your EDITED version of the question, I see you're trying to concatenate data from multiple cells into one cell so you can access that one cell from your web page. I think you'll find that it won't work that way. Your web page will pull in the formula itself, which is the contents of the cell. Excel can display the results of the formula, but the cell still only contains that formula and not what you're looking for.

A better way to do it would be to use a programming language like PHP which can pull the contents of many cells, check their contents, concatenate what you want or bullet it if you want.

Another thing you need to consider is that Excel spreadsheets are NOT a good way of creating a database for web content. You're going to run into problems with it that way.

Collapse -

I don't know PHP just yet

by little_dobby In reply to ???????

I'm starting with what I know how to do. I use Excel to make changes to product pricing, and I know I need to build the database at the very least before I can have someone help me figure out how to use PHP or ASP and pull from the database. I want to have the database built in Excel, then exported to CSV and uploaded to the site for the site to pull data from.

I can't figure out how to paste my Excel formula without this site taking the html and converting it to html instead of text only. So it makes it hard to follow my formula. If you know how I can even post my formula in text instead of html, that would be helpful, too! Thank you!

Collapse -

OK, then....

by ThumbsUp2 In reply to I don't know PHP just yet

Give me a little time and I'll work on it for you.

I presume there are multiple rows of data under the header columns you discribed in your first post and you want one column all the way down through the data to represent the results of concatenated cells in each row. You indicated that the last column used was BA. If you had a forumula in each row of column BB, assuming it's unused, that should suffice. Right?

edited to add: What bullet styles are you trying to use?

---

Collapse -

Yes, you are correct

by little_dobby In reply to OK, then....

Yes, column BB would contain the results of the concatenated cells. I already came across some issues with copying the Excel cell and pasting it into my HTML editor, but I found that the problem dealt with Excel retaining digits and spaces that I didn't want. That is why I put =CLEAN at the front of the formula, which on testing it worked perfectly.

This formula is mainly for the 600+ products from one vendor who has the product data for the bullets listed in multiple columns. I have a website now where we have manually created every product page for products from different vendors than the one with the multiple columns. I need to convert all those pre-made product pages to have their info. in this same database, so as far as I know, I have to go into Frontpage for every single product page we already have made and copy / paste the product description into a cell and then copy / paste the html code for the product's bulleted list into the next cell of that row. But that's another story...

Collapse -

Give this a try....

by ThumbsUp2 In reply to I don't know PHP just yet

Part of the problem for the formula not finishing the rest of the fields after it encounters a blank cell in AQ is because of your logic. If a blank cell has been encountered, the IF statement performs the TRUE portion of the statement and ignores the ELSE portion (the rest of the formula). So, you need an IF statement to check each and every cell and it all has to be contained within some "shell" formula so they're all looked at and processed.

No matter what I tried, there was no way I could use your method of coding the formula (using the & to concatenate multiple lines of a formula). At least, I couldn't make it work. Someone else might be able to.

So, pasted below is what I came up with instead. This formula isn't pretty, but it works (it's going to look terrible on this forum web page). I used the CONCATENATE formula to enclose everything. So, the logic of each individual cell won't effect other cell logic. It would be much better to put it into a macro to create your concatenated cell contents, but give this a try and see if it does what you want. Also, I added commas and spaces between each field in the concatenated results so that your web page can read the contents of one cell, yet know where each field begins and ends. I also added an asterisk to indicate empty cells. You can remove that stuff if you don't want it.


=CONCATENATE(CLEAN($AP2),", ",IF(ISBLANK($AQ2),"*",$AQ2),", ",IF(ISBLANK($AR2),"*",$AR2),", ",IF(ISBLANK($AS2),"*",$AS2),", ",IF(ISBLANK($AT2),"*",$AT2),", ",IF(ISBLANK($AU2),"*",$AU2),", ","UL or ETL Listed? ",IF(ISBLANK($AZ2),"*",$AZ2),", ","Watts: ",IF(ISBLANK($BA2),"*",$BA2),", ","Warranty: ",IF(ISBLANK($AW2),"*",$AW2),", ","Warranty Language: ",IF(ISBLANK($AX2),"*",$AX2))


Hint: Make sure you copy from this web page and paste into Notepad first, make sure it's all on one line, then copy FROM Notepad into the formula bar of Excel. Also, if you're copying and pasting from Excel into Frontpage, use the same technique. You need to run everything through Notepad first before it's pasted into Frontpage to strip it of all hidden formatting.

Hope this helps.

---

Collapse -

Thank you!

by little_dobby In reply to Give this a try....

Thank you very much for your help! I will give it a try and see if I can get your formula to work for my spreadsheet. I really appreciate your help. I'll let you know how it goes.

Susie

Collapse -

What exactly are you trying to do.

by ThumbsUp2 In reply to Excel Help Needed -Add mu ...

In your original post, it sounded as if you were asking for help with an Excel formula. Now, you've edited your post and it appears as if you're asking for help with FrontPage trying to EXTRACT the data from a spreadsheet and get it to format properly without making blank "bullets" on the web page where there is no data in the database.

So, I'm very confused. What exactly are you needing help with? Excel, or Frontpage?

Collapse -

Both, really, but Excel for this post

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

I need to know how to do both of these tasks, but for this post, I was really just asking how to combine cells into one cell while skipping cells that don't have any data, including skipping the &"Data Header" part for those same cells.

Collapse -

re: both really

by ThumbsUp2 In reply to Both, really, but Excel f ...

Getting data ready to be used in FrontPage is the easy part. Getting FrontPage to do what you want it to do with that data is nearly impossible. Trust me, I've used it for years.

By the way, in your original post, you said: "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"

FrontPage isn't designed to make decisions based on blank/non-blank data. In FrontPage, you're going to get a blank bullet for cells that are blank, no matter what you do. You'll need programming, such as ASP or PHP, to do this, as well as a server that can handle that programming.

Collapse -

Thanks

by little_dobby In reply to re: both really

Well, that's what we're aiming for eventually. But I've gotta start somewhere, so I'm starting with the database for now... But thank you for your input!

Back to Software Forum
18 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums