locksmith55
New Member
- Joined
- Mar 6, 2022
- Messages
- 22
- Office Version
- 365
- Platform
- Windows
Hello,
I am trying to make a dynamic formula in excel that will read when there is 1 inside of a cell and will print the serial number that is above it in column I. I need the formula to work row by row and left to right. If there is a better way to do this using helper columns or chaning the date but still getting the same result, please let me know.
Would prefer if the solution is not too complex. Helper columns are welcome.
Appreciate any help I can get!
I am trying to make a dynamic formula in excel that will read when there is 1 inside of a cell and will print the serial number that is above it in column I. I need the formula to work row by row and left to right. If there is a better way to do this using helper columns or chaning the date but still getting the same result, please let me know.
Would prefer if the solution is not too complex. Helper columns are welcome.
Appreciate any help I can get!
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Serial Numbers | Rows to print of serial number when 1 | 3 | HOW I WANT IT TO LOOK | Just to better visualize data | |||||||
2 | VNR12456 | BRD32561 | AKK98631 | BRD32561 | these are from apple row 3 | |||||||
3 | Apple | 0 | 1 | 0 | Use of helper columns are welcome | BRD32561 | ||||||
4 | Mango | 0 | 1 | 1 | BRD32561 | |||||||
5 | Pear | 0 | 1 | 0 | BRD32561 | these are from mango row 4 | ||||||
6 | Banana | 0 | 1 | 1 | BRD32561 | |||||||
7 | Orange | 1 | 0 | 0 | BRD32561 | |||||||
8 | AKK98631 | these are from mango row 4 | ||||||||||
9 | AKK98631 | |||||||||||
10 | AKK98631 | |||||||||||
11 | BRD32561 | these are from pear row 5 | ||||||||||
12 | BRD32561 | |||||||||||
13 | BRD32561 | |||||||||||
14 | BRD32561 | these are from banana row 6 | ||||||||||
15 | BRD32561 | |||||||||||
16 | BRD32561 | |||||||||||
17 | AKK98631 | these are from banana row 6 | ||||||||||
18 | AKK98631 | |||||||||||
19 | AKK98631 | |||||||||||
20 | VNR12456 | these are from orange row 7 | ||||||||||
21 | VNR12456 | |||||||||||
22 | VNR12456 | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2 | I2 | =C2 |
I3 | I3 | =C2 |
I4 | I4 | =C2 |
I5 | I5 | =C2 |
I6 | I6 | =C2 |
I7 | I7 | =C2 |
I8 | I8 | =D2 |
I9 | I9 | =D2 |
I10 | I10 | =D2 |
I11 | I11 | =C2 |
I12 | I12 | =C2 |
I13 | I13 | =C2 |
I14 | I14 | =C2 |
I15 | I15 | =C2 |
I16 | I16 | =C2 |
I17 | I17 | =D2 |
I18 | I18 | =D2 |
I19 | I19 | =D2 |
I20 | I20 | =B2 |
I21 | I21 | =B2 |
I22 | I22 | =B2 |