TheMacroNoob
Board Regular
- Joined
- Aug 5, 2022
- Messages
- 52
- Office Version
- 365
- Platform
- Windows
Hello Excel Experts,
I have a potentially novel request:
I have 22 sheets with the exact same formatting/cell positions. I would like to grab values from all sheets that meet criteria.
If Column C contains "Yes" on any sheet, I would like the sheet name (contained in cell A1), the Building Damaged (Column B), and the Deductible (Column D).
I was thinking something with the SMALL formula pumping out the next nth value fitting criteria of the lookup, or an array formula if that works better. I just can't seem to put it all together.
Any ideas on solutions or links to similar problems I can study?
I appreciate any help you can provide. I could do this manually but where is the fun in that? Please see below for sheet detailing exactly what I described above.
I have a potentially novel request:
I have 22 sheets with the exact same formatting/cell positions. I would like to grab values from all sheets that meet criteria.
If Column C contains "Yes" on any sheet, I would like the sheet name (contained in cell A1), the Building Damaged (Column B), and the Deductible (Column D).
I was thinking something with the SMALL formula pumping out the next nth value fitting criteria of the lookup, or an array formula if that works better. I just can't seem to put it all together.
Any ideas on solutions or links to similar problems I can study?
I appreciate any help you can provide. I could do this manually but where is the fun in that? Please see below for sheet detailing exactly what I described above.
| 1 | 2 | 3 | ||||||||||
| Sheets: | Lawyer | Harrison | Lake Heights | I want: | IF 'Lawyer:Lake Heights'!C:C="Yes", provide the sheet that contains the building (A1), the building name (B:B), and the deductible (D:D) | |||||||
| Sheet Format | ||||||||||||
| A | B | C | D | E | ||||||||
| 1 | Sheet Name | |||||||||||
| 2 | Sheet Name | Building | Deductible | Total | ||||||||
| 3 | Lawyer | Building A | $ 100,000 | $ 3,032,750 | ||||||||
| 4 | Lawyer | Building C | $ 321,000 | |||||||||
| 5 | Lawyer | Building E | $ 135,000 | |||||||||
| 6 | Bldg # | Damaged? | Deductible | Lawyer | Building F | $ 86,000 | ||||||
| 7 | Building A | Yes | $ 100,000 | Harrison | Building 2 | $ 168,000 | ||||||
| 8 | Building B | $ 112,000 | Harrison | Building 4 | $ 268,000 | |||||||
| 9 | Building C | Yes | $ 321,000 | Harrison | Building 11 | $ 152,750 | ||||||
| 10 | Building D | $ 95,000 | Lake Heights | Building A | $ 423,000 | |||||||
| 11 | Building E | Yes | $ 135,000 | Lake Heights | Building G | $ 682,000 | ||||||
| 12 | Building F | Yes | $ 86,000 | Lake Heights | Building W | $ 127,000 | ||||||
| 13 | Lake Heights | Building X | $ 52,000 | |||||||||
| 14 | Lake Heights | Building Y | $ 320,000 | |||||||||
| 15 | Lake Heights | Building Z | $ 198,000 | |||||||||
| 16 | ||||||||||||
| 17 | ||||||||||||
| 18 | ||||||||||||
| 19 | ||||||||||||
| 20 | ||||||||||||
| 21 | ||||||||||||
