Selecting a value from multiple cells

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Active Member
Joined
Jun 1, 2004
Messages
337
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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:

Code:
=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
 

Forum statistics

Threads
1,136,272
Messages
5,674,748
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top