I have been working on 2 linked workbooks for the last couple of weeks. The 1st workbook is a template for a registry of all of the items we have within our organization. The 2nd workbook is a template for inventory of items assigned to all people, places, and vehicles in our organization. I have formulas that link the 2nd workbook to the first workbook so that each time an item is assigned to a person, place, or vehicle, it will automatically show up on the inventory worksheet. The problem I have is that the formulas are static and require manual updating if we are to add new items to the 1st workbook.
I also have a VBA code to lock cells after data has been entered, which is great for protecting data and formulas. However, it does not allow for sorting, which would be a very helpful function to have.
Does anyone have some VBA codes that would allow for:
1) automatically creating named ranges for new items added to the 1st workbook so that they will be included in the 2nd workbook?
2) sorting and filtering of locked cells in a protected worksheet
Any help would be much appreciated.
Hi there, regarding the issues specified:
1. I guess you need to make a dynamic named range in wkb 1 and refer to it wkb 2
2. Sorting and Autofiltering is an option to allow when protecting sheet
Best regards, Eugene
Hello, my name is Cristian, I have a degree in Business and work with excel every day. I have much experience with spreadsheets, formulas, models and macros.
Check my reviews. They speak for themselves.
Best regards
Hi,
We are experts in Excel reporting, dynamic formula creation, designing dashboard, VBA macro for automation. We can do your job. However if you can share the Excel files it would be great.
Regards,
Pal
Hola
Soy especialista en tecnologías MS.
Lo que te sugiero es migrar tu base de datos a una tecnología más idónea para el almacenamiento de datos como Access o mejor SQL Server.
Excel puede acceder facilmente a la data almacenada en bases de datos relacionales
Saludos
Carlos
I have the way to lock, unlock dynamically your sheets in order to sort them and let it protected before save. Or immediatelly after sorted.
I did it for a personal project about a POS, which I use to sale currently.
It is tested , and proven. no doubts.
Let us chat if interested, Please read the profile and comments from my clients as a reference.
I have extensive experience building models with vba automation. Your solution is in two parts.
part 1: manage names ranges so that new data extends those ranges such that the new item is reflected in the inventory... (solution depends on the excel version in use!). Update formulae to be dynamic (if possible) rather than static (again, depends upon excel version in use)
part 2: sorting and filtering locked sheets. We can partially replicate this functionality in a button/field combo... the attached code will essentially (unlock the sheet, perform the sort/filter/ relock the sheet)
Details will again depend on the excel version.
HI.
I AM ENRICO.
I BELIEVE I CAN DEVELOPE THE CODE FOR YOU AS MY SKILL SET INCLUDE DATA INTEGRATION,
ME EXCEL ,MS ACCESS AND VBA CODING.
PLEASE FEEL FREE TO APPROACH ME FOR FURTER ASSISTANCE .
KIND REGARDS ,ENRICO
I'm a maestro in excel with 6+ years exp. Just a piece of cake for me. Have already created work books like this. You don't even need a VBA code for this. Can be achieved with a combination of excel formulae
Hi there,
I would suggest a named range as you mention. With the locking of cells, what is the business process around this? Is the spreadsheet accessed by multiple people, or controlled by one person? What happens if you make a mistake in entering data?
I propose two buttons on the second spreadsheet, one to lock/unlock data, the second to sort (with an option to choose which column to sort by)
Cheers
Josh