Pull data from various Named Ranges

Hi,

I can't help thinking that you'd be better served by creating a proper database and using that to analyse your data with say a Pivot Table. e.g. create a database with the following column labels

Customer
Room Number
Cleaned (where the values would be either Yes or No/blank

Then add a row for every permutation of customers and rooms and mark the database when the rooms are cleaned. Since this is needed every season you need to decide whether you want to add a fourth column to record the date/season or just clear the Yes/Cleaned and start afresh every season.

Then just apply a Pivot Table and put the 'Cleaned' field in the PT Report Filter pane so that you can select the blanks/No when you want to list the uncleaned rooms.

This would be a far more elegant and useful approach than what you are attempting to do at the moment.

Put this formula in B9 and fill down

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

Select the range B9:B13 and enter this formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

On the Database worksheet enter the room numbers in H2:H6
Enter this formula in G2 and fill down:

Formula:

Please Login or Register  to view this content.

In I2 enter this formula and fill down to give the remaining rooms to be cleaned:

Formula:

Please Login or Register  to view this content.

Select I2:I6 and name Clean

Select Selection!F9:F13 and enter this formula:

Formula:

Please Login or Register  to view this content.

Array Enter (Ctrl + Shift + Enter)

I didn't notice the two numbering systems for the rooms. Will get back to you.

Ok, extensive changes to the formulae to take care of the rooms to be cleaned.

On the Selection worksheet select the Rooms # B9:B13 and name the range Rooms

On the Database worksheet select H2:H6 and ARRAY enter this formula which will change the room #s according to the customer:

Formula:

Please Login or Register  to view this content.

In Selection ARRAY enter in I2 and fill down:

Formula:

Please Login or Register  to view this content.

In Selection ARRAY enter in J2 and fill down:

Formula:

Please Login or Register  to view this content.

Name J2:J6 Clean (re define the name in the Name Manager)

I had trouble with the previous message so ended it ASAP.
When you change customer, the selections for the rooms to be cleaned are not automatically changed you have to delete the existing entries and make new choices from the drop down lists.

The room numbers will change automatically and the Room Left to be Cleaned will fill with all the rooms for that customer until you delete the Rooms Cleaned data and make new entries. As you make entries in the Rooms Cleaned, that entry is eliminated from the Rooms Left to be Cleaned.

I hope that makes sense....it has been a long day

You're welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.