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?

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.

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

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

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 listhttps://support.microsoft.com/en-us/office/add-or-remove-items-from-a-drop-down-list-0b26d3d1-3c4d-41f5-adb4-0addb82e8d2c?ui=en-us&rs=en-us&ad=usMore on data validationhttps://support.microsoft.com/en-us/office/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c?ui=en-us&rs=en-us&ad=us I would be happy to know if I could help. NikolinoI 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.

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.

M2M FORMULA | Microsoft Community Hub

11 Replies

NikolinoDE
Gold Contributor
Nov 18, 2020
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-41f5-adb4-0addb82e8d2c?ui=en-us&rs=en-us&ad=us
More on data validation
https://support.microsoft.com/en-us/office/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c?ui=en-us&rs=en-us&ad=us

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.
- melkhor17
  Copper Contributor
  Nov 19, 2020
  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.
  - NikolinoDE
    Gold Contributor
    Nov 19, 2020
    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)
melkhor17
Copper Contributor
Nov 18, 2020
originally, the sheets named were 2020,2021,2022,2023
- Riny_van_Eekelen
  Platinum Contributor
  Nov 18, 2020
  melkhor17 There are hundreds of templates out there. which one are you using? Can you upload it here?
  - melkhor17
    Copper Contributor
    Nov 19, 2020
    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.
    P&L template copy.xlsx141 KB
    62941CFE1354.xlsx280 KB

Forum Discussion

M2M FORMULA

11 Replies

Resources