| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 115431 | 2011-01-18 00:41:00 | Excel query | gary67 (56) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1170636 | 2011-01-18 00:41:00 | Any excel gurus out there? I am trying to get cells in sheet 3 to update from sheet one. If sheet one say D3 contains a + number I want that number to be in say D3 of sheet three. However if it contains a - number I want it to show a zero in sheet 3 |
gary67 (56) | ||
| 1170637 | 2011-01-18 01:16:00 | Ok so I have worked out how to get the data into sheet 3 but just need to get any negative numbers to return a zero. I have so far =sum(sheet1!D3) |
gary67 (56) | ||
| 1170638 | 2011-01-18 02:45:00 | Phew 3 hours with google and playing around I end up with =(max(0,sheet1!D3)) which returns zero if the D3 cell displays a negative number, and have set custom formatting to return all positive numbers in red bold with yellow fill |
gary67 (56) | ||
| 1170639 | 2011-01-18 03:11:00 | OK ... I spent 20 minutes trying and almost got there ... but can you tell me why you want a negative number to show as a 0 ... and please explain the most unpleasant colour scheme forced upon poor old D3 of sheet three ... :D | SP8's (9836) | ||
| 1170640 | 2011-01-18 04:32:00 | I would use an IF statement personally, but if the MAX one works then all good =(IF(Sheet1!D3<0,0,Sheet1!D3)) It basically says if D3 in sheet 1 is less than 0 put a zero in the cell, if it's 0 or higher then show me what was in the cell |
Halwende (3418) | ||
| 1170641 | 2011-01-18 06:39:00 | Thanks, that was the one I was trying to get to work but couldn't get it quite right I'm not much of an Excel person. Sheet one and two contain lists of groceries in stock and sheet three is the shopping list and so I wanted sheet 3 to only show positive numbers of amounts to be bought that month and so the colour scheme is to make it stand out. yes its a rainy day and I was bored |
gary67 (56) | ||
| 1170642 | 2011-01-18 07:23:00 | I was trying to do it Hal's way with the IF and <0 .... couldn't quite get it right either and think it was because I missed one set of () | SP8's (9836) | ||
| 1170643 | 2011-01-18 18:42:00 | I was trying to do it Hal's way with the IF and <0 .... couldn't quite get it right either and think it was because I missed one set of () Same here knew it should work, then found that other way via google and with a slight adaptation to suit my sheets it works |
gary67 (56) | ||
| 1 | |||||