M2M FORMULA

[melkhor17]

Hi there 

I have downloaded an excel template for my company profit & loss. However I plan to change the worksheet names to 2019,2020,2021,2022,2023 and so forth. However, when I change the sheet name, the drop down boxes and data does not follow. How do I change it so the data follows according to the year?

 

[#2]

originally, the sheets named were 2020,2021,2022,2023

[#3]

@melkhor17 There are hundreds of templates out there. which one are you using? Can you upload it here?

[#4]

@melkhor17 

Suggested solution 1:
File -> Options -> Advanced-> Group: Show options for this workbook: -> Show objects as: Select "All".

Then the dropdown functions should be back. :)

Suggested solution 2:
Either save as the workbook as .xlsx, or name the dropdown source (the new worksheet) with the same name as the previous sheet.

Add. Infos:

Add or remove items from a drop-down list

https://support.microsoft.com/en-us/office/add-or-remove-items-from-a-drop-down-list-0b26d3d1-3c4d-4...

More on data validation

https://support.microsoft.com/en-us/office/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0...

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#5]

Hi @Riny_van_Eekelen the copy is what i intend to edit and the other is the original template. have tried to use data validation but doesn't seem to work.

[#6]

@NikolinoDE hi there, i tried to use data validation and selected all but it doesn't seem to work. I have attached a copy of the file above just for reference.

[#7]

@melkhor17 

The file (62941CFE1354.xlsx) is password protected. Please unlock this file to continue.

Thank you for your patience and time.

Nikolino

I know I don't know anything (Socrates)

[#8]

@melkhor17 As always, such templates are difficult to amend if you don't dive into them at the deep end. This one has two hidden sheets Control1 and Control2 with hard coded references to tabs 2020 to 2025. Adding a tab with a different name will not do anything to the month-to-month comparison. Quite a silly template IMHO.

I've changed the formulae in the two hidden sheets for you and believe it works now. 

[#9]

@Riny_van_Eekelen Thank you so much!! You just made an impossible deadline now nearly possible. God bless you.

[#10]

@Riny_van_Eekelen for me to change the P&L tab, do i just use data validation to do the same for me to get 2019 data into it as well?

[#11]

@melkhor17 Added a column 2019 for you in the P&L tab. It links to the last column in the 2019 sheet which was given the name "Year2019", similar to the other sheets. does this help?

[#12]

@Riny_van_Eekelen it really does. thank you so much!