My basic issue is, can I apply a Formula which use the Cell value as condition?
Now let me explain what I need.
I have a Spreadsheet having list of inventoried Aluminium extrusions. I want Counts to display, where some materials are inventoried by the Pieces, others by the Size(Foot).
I want to count all materials by the Pieces, and not worry about converting anything to linear feet.
To simplify what I am trying to do, Let's say I have set up like this.
Column A Column B Column C Column D Column E Color/type Length Unit Of Measure Pieces counted Converted to LF Red angle 20 LF 31 64 Blue tube 16 LF 10 160 Orange angle 20 EA 62 1240 Black channel 24 LF 43 1032 Purple angle 16 EA 71 1136 Green tube 12 EA 34 408
Column E contains Formula, which multiplies the value in Column D with Column B, resulting in the linear Footage on hand.
Conditional formatting applied in Column E and the Criteria is,
if the value in column C is "EA" or "LF", and the column D has values, so that only the proper unit of measure for each material's Count can be seen.
This makes the counting difficult though since, the cells for anything tracked by "EA" in column C are BLACK whether there is anything in them or not. So I'm losing the place, as I look from the screen to the page and back.
What I would ideally like to do, if possible, column E automatically multiply the value entered into column C by the number in column B if the value in column C is "LF", but display the number exactly as entered in column D if the corresponding value in column C is "EA".
Ultimately, my end result should display like so.
Column A Column B Column C Column D Column E Color/type Length Unit Of Measure Pieces counted Final # to enter Red angle 20 LF 31 64 Blue tube 16 LF 10 160 Orange angle 20 EA 62 62 Black channel 24 LF 43 1032 Purple angle 16 EA 71 71 Green tube 12 EA 34 34
I lost the way to do this, although it's probably simpler than I am making it. I have inspired from the site explains "Apply formulas conditionally in Excel, based on text contents of another cell".
My knowledge on Excel is completely self-taught by trial and error, which of course means I really only know just enough to get myself in trouble. Any help on this would be greatly appreciated! Thanks!