# Selecting a value from multiple cells

#### Yusuf

##### Active Member
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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

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

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

Replies
4
Views
272
Replies
1
Views
73
Replies
0
Views
280
Replies
5
Views
332
Replies
1
Views
177

1,217,382
Messages
6,136,238
Members
450,000
Latest member
jgp19

### 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.

### Which adblocker are you using?

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

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