hsandeep
Well-known Member
- Joined
- Dec 6, 2008
- Messages
- 1,213
- Office Version
- 2010
- Platform
- Windows
- Mobile
My input data is in Range1
Range1: B2:G12; values are either of +ve or -ve values or zero or null, columns are contiguous.
There is another ‘flag’ range which is below Range 1 i.e. below B2:G12 at B14:G14.
B14:G14 is either of 100 or <>100 with a caveat that at a time, at the most, only one 1 cell of B14:G14 may contain 100
Output range: J2:J12 (for which formula is requested) as detailed below
For J2:J12:
Example for J2:
Formula should look for value 100 in ‘flag’ range B14:G14 & then find its analogous cell’s value in the corresponding row of Range1. Here G2 for row #2 since 100 found in G22.
How to accomplish please?
Sheet2
<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 5px"><col style="WIDTH: 9px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>
Range1: B2:G12; values are either of +ve or -ve values or zero or null, columns are contiguous.
There is another ‘flag’ range which is below Range 1 i.e. below B2:G12 at B14:G14.
B14:G14 is either of 100 or <>100 with a caveat that at a time, at the most, only one 1 cell of B14:G14 may contain 100
Output range: J2:J12 (for which formula is requested) as detailed below
For J2:J12:
Example for J2:
Formula should look for value 100 in ‘flag’ range B14:G14 & then find its analogous cell’s value in the corresponding row of Range1. Here G2 for row #2 since 100 found in G22.
How to accomplish please?
Sheet2
| B | C | D | E | F | G | H | I | J | |
| 2 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | ||
| 3 | 99.00 | 1.25 | 28.00 | 28.00 | 41.25 | -88.00 | -88.00 | ||
| 4 | 99.00 | 28.00 | 28.00 | 28.00 | 41.25 | -80.00 | -80.00 | ||
| 5 | 88.00 | 28.00 | 28.00 | 28.00 | 41.25 | -80.00 | -80.00 | ||
| 6 | 28.00 | 28.00 | 28.00 | 28.00 | 41.25 | -80.00 | -80.00 | ||
| 7 | -100.00 | 20.00 | 20.00 | 20.00 | 1.00 | 5.00 | 5.00 | ||
| 8 | 1.00 | 0.00 | 0.00 | 0.00 | -65.00 | 50.00 | 50.00 | ||
| 9 | -85.00 | 0.00 | 0.00 | 0.00 | -65.00 | 50.00 | 50.00 | ||
| 10 | |||||||||
| 11 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 12 | 0.00 | 0.00 | |||||||
| 13 | |||||||||
| 14 | 0 | 0 | 0 | 0 | 0 | 100 |
<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 51px"><col style="WIDTH: 5px"><col style="WIDTH: 9px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>
Last edited:
