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

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top