Need some help with excel

[P-Dub]

Okay, i know this forum rocks for hardware, and having seen some of the stuff in this forum, i think i might be on to a winner with someone who can actually help me.

I have been given the task of trying to sort out an Excel spreadsheet, unfortunatley i cannot put it on here because of the nature of the company i work for, but if you guys could take a look at the code i'm trying to work, and let me know why it isnt working i would really appreciate it.

Basically, i'm trying to get a formula to check 2 cells are the same and if they are, via a simple IF statement print either nothing, or a zero as follows...

=IF(A1=B1,IF(C2="Y","0",""),"")

The output needs to be a number, but even removing the quotation marks and putting it is as follows

=IF(A1=B1,IF(C2="Y",0,""),"")

Doesnt seem to get it to work, it always returns FALSE, which i dont think it should do, i think i've narrowed it down to the comparison (A1=B1) not being right, but i'm a little confused as to what i'm supposed to use instead.

Basically, i'm stumped, i've been dumped with this job mainly because i'm the only remotley IT'ish person here ("You play computer games and build them? Here design us a new working spreadsheet") and tbh, they didnt listen when i told them i dont normally do Excel.

If anyone can help, i would be very appreciative.

Dubz

[XS_Rich]

Works fine for me. I'd ensure that the cell the formula is in has 'General' formatting on it, as it could be formatting it as a TRUE/FALSE cell.

You could always put a cell next to the formula reading =A3+1 (assuming formula in A3) and seeing if it comes out as 1.


PS I take it you've put a 'Y' in C2.

[P-Dub]

Just tested it again, and yes, the format i showed you does work, but when i try to add in the next part of the formula, it all goes back to rat poo...

=IF(E21='Standards and controls'!B18,IF('Standards and controls'!D19="Y",0,""),"FAIL")

I always get FAIL returned. I think i might have to think a little more laterally on this one and see if i can sort it out on another section of the spreadsheet.

I hate my job sometimes.

Dubz

[XS_Rich]

Very strange, it looks fine to me.

So where do you work in Yorkshire? (I recently moved away from Leeds)

[Anemone]

I think you might have a value problem. I pasted your formula into Excel 2003 for some testing and it worked fine. I'd test your A=B. Just put in a cell =A1=B1 and see if you get a true or false where you expect to. If you don't I have a sneaking suspicion you might have "text" values that look like numbers. Sometimes when you are importing data, you can get a text value that looks like a series of numbers but Excel doesn't think so. To change you can use the =value(a1) in an adjacent cell, say c1 and copy and paste the values from c1 back into a1.

Now if it's not a value problem then you'll need to do some poking into your basic a1=b1 and examine conditions as to where it should be true, but isn't. When analyzing IF statement that aren't working, especially nested IF's, as above with the second stage checking if c1="Y", you want to check the conditions at each IF to be sure they are returning true or false where you'd expect them to. Just cut out the true/false portion and see if it gives true or false or an error and you'll quickly narrow down your problem.

[P-Dub]

I work in York, used to live in Garforth near Leeds. Not the nicest place but was cheap and near enough to work that i didnt offend my working class roots. :)

I've managed to sort this out now, and have moved on to amalgamating 5 spreadsheets into 1, 4 of which fit easily, but the last one is proving to a bit of a bitnatch. I'll beat it into submission though, but i appreciate the tips there Anemone, very useful. :)

[Marci]

P-Dub - have a poke at doing it with MySQL database tables, using a table per spreadsheet, and PHP for all your functions - you may find it all becomes MUCH simpler without Excel's irritations getting in the way, once you've got a framework in place, with the bonus of being able to web-frontend it all, thus reducing the pre-requisite need for Office licenses on every client, and allowing folks to work from out-of-office and still from a central db... may score you some browny points and an excuse for a payrise too! The logic statements n' such are very similar, but a lot more adaptable to your needs.