Selecting a value from multiple cells

[Yusuf]

Hi

I'm trying to return a value on the same cell number but over seven different sheets.

An entry can only be made on one sheet at a time.
I don't really know how to create an example but if the Cell A1 was empty on Sheets 1 to 6 and an entry is made on Sheet 7 then I would like that value returned.

 

[pgc01]

Hi Yusuf

If the value is numeric, you can use a 3d formula

=SUM(Sheet1:Sheet7!A1)

Since you say that only one of the seven cells has a value and the others are empty, the sum is equal to the value of the non empty cell.

If this does not meet your conditions, please post again with more details such as examples with inputs, locations and expected outputs, etc.

Hope this helps
PGC

 

[Yusuf]

Hey there

It's names unfortunately.
I'm currently using & between referencing the cells which is working but would ideally like to use something that has a bit more security.
The cells where data is entered is actually a Validation List and it's only to be used by 1 of seven managers at a time as I'll mail this workbook to them.

Do you know of anything I could try?

Cheers
Y

 

[pgc01]

Hi again

Unfortunately 3d formulas work with numbers.

In this case you can use an array formula. Since you have 7 worksheets it's better you don't write their names directly in the formula or else the formula will be very long.

In this example I wrote the names of the 7 worksheets in E1:E7.

Try:

=INDIRECT("'"&INDEX(E1:E7,MATCH(TRUE,T(INDIRECT("'"&E1:E3&"'!A1"))<>"",0))&"'!A1")

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Hope this helps
PGC