Formual with Tab Name obtained from A Cell

[alex.j.stewart]

I have a workbook that has multiple tabs each with a team members name.

I have an additional tab that is summary tab that pulls together all of the data from the individuals tabs with the team member names in column B.

I am using the formula =IF(Stuart!B$3="","",Stuart!B$3) to copy data from the team members tab. Is there anyway I can write the formula so that it determines which tab to look in by referencing a cell in column B on the summary sheet with the tab name in?

[Tony Valko]

Something like this...

A1 = some sheet name like Stuart.

Then, the formula would be:

=IF(INDIRECT(A1&"!B3")="","",INDIRECT(A1&"!B3"))

[alex.j.stewart]

Tony, thank you for the quick reply. So you solution does exactly what I asked for but it turns out I didn't ask for everything I require.

So I have used your formula in column C row 3 of my summary tab referencing column B row 3 which is the tab name =IF(INDIRECT($B3&"!B3")="","",INDIRECT($B3&"!B3")) and this gives me exactly what I expect. However when I copy it to column D I want it to read =IF(INDIRECT($B3&"!C3")="","",INDIRECT($B3&"!C3")) but it doesn't auto update to the next column, it still looks at cell B3 on the named tab. is it possible to write the formula in such a way that is would do this and continue on along the columns

[Jonmo1]

Try

=IF(INDIRECT($B3&"!"&CELL("address",B3))="","",INDIRECT($B3&"!"&CELL("address",B3)))

[Tony Valko]

Try it like this with a caveat:

=IF(INDIRECT($B3&"!"&CELL("address",B3))="","",INDIRECT($B3&"!"&CELL("address",B3)))

The caveat being that there is no "bulletproof" way to increment cell references within the INDIRECT function.

If you were to insert a new column A on the sheet where this formula resides then the reference to CELL("address",B3) would change to CELL("address",C3) which could lead to incorrect results.