Define New Names for Specific Cells

hi all

i need your help please, check both attached files; before and after.

i need to sort column A from A to Z

then

define new names, if same value in column A, then referes the name to all cells contains same values from column A to F.

i can do it manually each time, but what if new rows added? i need some macro to do the sort then to check and do all define names in same sheet in my criteria.

thanks.

Hi

I don't know what Z_1 is about so I leave you to sort out.

The rest is sorted by this workbook.

hi,

i think that i have to explain more, i have two idea to solve my problem but i dont know will be solved by formula or macro,

first;
i need to sort data in column (lowest to highest), then making the define name using arrays, lets say that after sorting i have Value="A-1" in 16 rows, so i need array to select these 16 rows. if i add one row later on which include value "A-1", again will sort....define name....17 rows will be selected in array.

second;

i have in sheet one, in Cell: A1, A-1 as a Value.
i want to click on this Cell which contains A-1, will hyperlink to the second sheet and filter column A with the data value matching with Cell A-1 from the first sheet.


i think first one will be macro and second one will be formula, and i prefer the formula

many thanks in advance

any help please?

hi jaslake,

actually if i add another row in "before" sheet, will not appear in "after" sheet, lets add duplicate A-18 row and run the macro, it will appear one time only.

ok, please just i need a macro will check all data in "before" sheet and will do;

1- sort (lowest to highest)
2- re-assign the names to include all new data (rows)

forget about "after" sheet, and about the hyperlinks, i will do it manually later on like to click on for example B-2 in another sheet (i will create it as a master list), will hyperlink to all B-2 in A column, and just to select them from column A to column F.

thanks.

thanks a lot john

just i have one question;

what about if i have header in my table and i dont want to do anything with?

thanks.

please tell me what are the changes u did in the code u gave me in post no. 7?

thanks a lot john
it is perfect now