Manipulating 3D Formulas based on value in a specific cell

[bradlehoux]

I'd like to be able to have a 3D formula change based on the value I place in a specific cell. The 3D formula sums the same specific cell across the workbook.

In the attached workbook I want to input a number in cell E2 and then have the 3D formulas in column F reference that cell to know what tab data to sum.

[PeteABC123]

Please check your file name and file type. xls. in the download does not match the xlsx file type, and is throwing our security scan into a tizzy.
Pete

[XOR LX]

Hi,

In F10 of the ESTIMATE CONSOLIDATED sheet:

=SUMPRODUCT(N(INDIRECT("'Change Order #"&ROW(INDEX(A:A,1):INDEX(A:A,$E$2))&"'!"&CELL("address",B31))))

This can be copied down to F14 without further amendment. It can also be copied to F8 and F16, though in those cases you will need to manually amend the cell being referenced (highlighted red in the above).

Regards

[bradlehoux]

Thanks worked like a charm

[XOR LX]

You're welcome!

Cheers