1

I am an Excel and Xpath noob (well, at least I am honest!). I am working on a project that, at present, I have got working through learning from online tutorials. However, the next step has me stumped!

I am hosting a document on Google Sheets and as such, want to try and avoid VBA (which I have no idea about yet, anyway.....) and hope I can multi function/formula in one cell instead.

I am using Xpath to populate a cell with an input from an XML document. The function is:

=ImportXML("https://api.eveonline.com/corp/StarbaseList.xml.aspx?keyID=*******&vCode=***********","//row[@itemID='*******']/@state")

There are only two possible cell outputs from this function - "1" or "4" and currently have this working fine.

However, I require to change the cell display from "1" to "OFFLINE" in red text, and "4" to "ONLINE" in green text, but do not even know where to begin doing this.......

Any help would be appreciated.

3
  • 3
    =IF(ComparisonHere = 1, "OFFLINE", "ONLINE") ? In context - =IF(ImportXML("https://api.eveonline.com/corp/StarbaseList.xml.aspx?keyID=*******&vCode=***********","//row[@itemID='*******']/@state") = "1", "OFFLINE", "ONLINE") - then use conditional formatting to change the colours. Only because you're certain there are only two outputs, if a 2 or 3 could be output you'll need a condition check to be sure it's returned a 4.
    – Jonno
    Jan 2, 2016 at 15:06
  • Easy when you know how! You are a true gent.....works as I need it to. HUGE thanks :)
    – Titch2K6
    Jan 2, 2016 at 15:29
  • No problem. Just to help you understand if there's any confusion, the formula is: =IF(Comparison, True, False). So, the first argument is what you're comparing (EG - Cell A1 > 1, Cell A2 = "Test"). The next argument is what the output will be if the result is true, the final argument is the result if false.
    – Jonno
    Jan 2, 2016 at 15:40

1 Answer 1

0

I'm posting this wiki so that it will have an answer and can be closed. All information is straight from the comments made by @Jonno


=IF(ComparisonHere = 1, "OFFLINE", "ONLINE")

In context:

=IF(ImportXML("https://api.eveonline.com/corp/StarbaseList.xml.aspx?keyID=*******&vCo‌​de=***********","//row[@itemID='*******']/@state") = "1", "OFFLINE", "ONLINE")

Use conditional formatting to change the colours. This only works because you're certain there are only two outputs. If the output could be a 2 or 3, you'll need a condition check to be sure it's returned a 4.

Just to help you understand if there's any confusion, the formula is =IF(Comparison, True, False). So, the first argument is what you're comparing (EG - Cell A1 > 1, Cell A2 = "Test"). The next argument is what the output will be if the result is true, the final argument is the result if false.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .