Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regonise coloum A while adding up coloum B to give a result in C
A B C
1 licence No. weight status 2 gmb123r 100 3 gmb234q 150 4 gmb789h 250 5 gmb123r 150 6 gmb123r 275 What I am trying to do is when you type in the licence number and the weight into coloums A&B, Coloum C returns weither or not the licence is "used" or "open" and to update for every new row added, recognising that it only takes away from the one licence total that has been entered. Licence maximum weights gmb123r=525 so when row 6 is entered all 3 rows would now say "used" Also if possible if you entered a weight that took licence gmb123r over its weight allowance coloum C would say "too much" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regonise coloum A while adding up coloum B to give a result in C
put in C2 and copy down
=IF(SUMIF($A$2:A2,A2,$B$2:B2)Maxwt,"too much","used") Replace "Maxwt" with formula(?) e.g. VLOOKUP, to get the maximum weight for a given licence number. HTH "B.H.JIG" wrote: A B C 1 licence No. weight status 2 gmb123r 100 3 gmb234q 150 4 gmb789h 250 5 gmb123r 150 6 gmb123r 275 What I am trying to do is when you type in the licence number and the weight into coloums A&B, Coloum C returns weither or not the licence is "used" or "open" and to update for every new row added, recognising that it only takes away from the one licence total that has been entered. Licence maximum weights gmb123r=525 so when row 6 is entered all 3 rows would now say "used" Also if possible if you entered a weight that took licence gmb123r over its weight allowance coloum C would say "too much" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regonise coloum A while adding up coloum B to give a result in C
"status"
C2: =IF(D2525,"OVER",IF(D2<525,"Open","Used")) "used" D2: =SUMIF(A:A,A2,B:B) "available" E2: =525-D2 For other examples of SUMIF, see SUMIF Example http://www.mvps.org/dmcritchie/excel/sumif.htm You could use Conditional Formatting to color rows or cells http://www.mvps.org/dmcritchie/excel/condfmt.htm --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "B.H.JIG" wrote in message ... A B C 1 licence No. weight status 2 gmb123r 100 3 gmb234q 150 4 gmb789h 250 5 gmb123r 150 6 gmb123r 275 What I am trying to do is when you type in the licence number and the weight into coloums A&B, Coloum C returns weither or not the licence is "used" or "open" and to update for every new row added, recognising that it only takes away from the one licence total that has been entered. Licence maximum weights gmb123r=525 so when row 6 is entered all 3 rows would now say "used" Also if possible if you entered a weight that took licence gmb123r over its weight allowance coloum C would say "too much" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regonise coloum A while adding up coloum B to give a result in C
See Topper's reply which was a whole day earlier and
a better answer and recognized that different trucks(?) would probably have different weight limits. Some help with VLOOKUP Worksheet Function mentioned can be seen in http://www.mvps.org/dmcritchie/excel/vlookup.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i count the number of cells used in a coloum? | Excel Discussion (Misc queries) | |||
Identify duplicate coloum A cells, and merge their row and text in | Excel Discussion (Misc queries) | |||
Need to import data to a coloum not a row | Excel Discussion (Misc queries) | |||
Chart Coloum Labels - Very Urgent | Charts and Charting in Excel | |||
entering a26-02 into a cell give strange result | Excel Discussion (Misc queries) |