Have Name Manager point of each sheet?

[lost_in_the_sauce]

I have a workbook with multiple (37) tabs for distribution. It currently runs VBA that sends each tab as an attachment to an email address in cell B5 of each tab.

In updating the file for ease of use, I have conditional drop downs so that someone cannot make the wrong selection from the second drop down after using the first one. This is with Data Validation -> List, Create From Selection to push to Name Manager, and =indirect()

I seem to be having a problem in that every tab's name manager is looking at the first tab, and when the tabs are sent off separately, they break because the name manager is pointing to a tab that is no longer there.

If I go into any tab's name manager, in the Refers To column it's always the first tab I did it in, so when they go out, the name manager gets cleared.

 

[James006]

Hi,

For your Named Ranges, have you checked their Scope ? (located just under Name)

 

[lost_in_the_sauce]

Hi,

For your Named Ranges, have you checked their Scope ? (located just under Name)

Scope is workbook. Looks like I can't use Name Manager here for the conditional drop down, because when I run the macro to send all the tabs out as individual emails, only the one with the original table reference keeps the Names and the rest break.

 

[James006]

When sending out your 37 worksheets, would you like each recipient to be able to use your data validation or is it a feature which could be frozen just before e-mailing the files ?

 

[lost_in_the_sauce]

When sending out your 37 worksheets, would you like each recipient to be able to use your data validation or is it a feature which could be frozen just before e-mailing the files ?

each sheet.

Basically it's monthly credit card receipts for corporate cards, they get the excel sheets individually, the first drop down is which group, the second is which function. I'm trying to limit what is in the function drop down based on what group they select so they aren't picking things they shouldn't when it gets sent back for AP to enter into our accounting software

 

[James006]

The quick patch is to replicate your data validation in each worksheet ..... go to an unused area, at the far right, and hide these columns ...

 

[lost_in_the_sauce]

The quick patch is to replicate your data validation in each worksheet ..... go to an unused area, at the far right, and hide these columns ...

But doesn't name manager point to a very specific table? I have the same data on each tab and have it hidden, but I thought the =indirect() needed name manager. I've been looking for a conditional drop down solution that doesn't use it.