Spanning 208 Worksheet Tabs

[galaska2006]

I have a need for pulling a singular amount from 208 tabs where the amount isn't necessarily always in the same cell. While there is a cookie cutter, its placement isn't always commencing at the same row on each tab. The first image illustrates a table of values where my goal is to pull the "call option value" located in this case in cell G66, using criteria in cells D46 (Grant Date) and D47 (Expiry Date) which should correspond with a set grant price. My table to be populated is the 4th image, while the 2nd and 3rd images illustrate the volume of tabs.

I considered a SUMIFS formula such as below:

=SUMIFS(Sheet2:Sheet1!$G:$G,
Sheet2:Sheet1!$D:$D,$A2,
Sheet2:Sheet1!$D:$D,$B2,
Sheet2:Sheet1!$C:$C,"call option value")

but it returns #VALUE! I would be open to VBA that can data mine across a span of tabs to seek and find the value in column G corresponding to the row with "Call Option Value", so it need not be a formula. Plan A is to just build the table manually, of course, but who likes manually when there's got to be a way? Thanks for any help or ideas.

 

[GlennUK]

Why don't you standardize the sheets to be exactly the same layout?

 

[galaska2006]

Thanks. Client company, not my mess of a file. Also, I’ve seen a need to span sheets before, so it’s not like that alone would fill the knowledge sought. But it isn’t a bad idea, just not what I’m seeking. Thanks!

 

[GlennUK]

Thanks. Client company, not my mess of a file. Also, I’ve seen a need to span sheets before, so it’s not like that alone would fill the knowledge sought. But it isn’t a bad idea, just not what I’m seeking. Thanks!

Regardless of where the data comes from, you are better off analyzing the differences, with a view to writing a macro to align the sheets. Then all is simple.