How to use 'indirect' to sum a column on another sheet

I want to place a formula in a cell on sheet 'A' that finds the column number that contains a particular value on sheet 'B' and sums that column. In other words, sheet 'B' has multiple columns with unique headers. I need a formula that I can place in a cell on sheet 'A' that matches a value to the headers on sheet 'B', uses that match value to identify the column on sheet 'B' where the match was found, and then sums that column.

Thanks for your help.

You don't need indirect for that..

with the formula on Sheet A
And cell A1 on Sheet A contains the "Value" you want to use to match in the headers of Sheet B.

=SUM(INDEX('Sheet B'!A:Z,0,MATCH(A1,'Sheet B'!A1:Z1,0)))

Make sure A:Z and A1:Z1 are large enough to handle all the columns on Sheet B

Thank you.

You're welcome.