• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Delete complete row where first cell is linked to sheetname|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Delete complete row where first cell is linked to sheetname|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Delete complete row where first cel…
sp_PrintTopic sp_TopicIcon
Delete complete row where first cell is linked to sheetname
Avatar
Tom VAN LOO

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 27, 2022
sp_UserOfflineSmall Offline
1
December 9, 2022 - 7:45 am
sp_Permalink sp_Print

Hi

I created a workbook based on several formulas and functions. I attached the document but it is in Dutch so I will try to explain the situation and my goal/problem. Most of the sheets are protected without password, sorry for that.

I started the workbook with a list of products that I used to create a number of worksheets with the name of every product. I then used the name of the worksheets to create a new list of products on the Index-sheet but added the hyperlink to the productname referring to its own worksheet. I used the GET.WORKBOOK method explained in Myndas video. Thats why the workbook is in a xlsm format. On every product-worksheet I then added a table to follow the stock of products left (with also the IN/OUT to be added manually on the date the transfer occures) 

Furthermore on the Index-worksheet in Column D, I added a column to put in manually the starting status in the beginning of the trimester. These are plain numbers, no formulas. In Column E on the Index-sheet, I also added a search function to return the last status of every product (with an INDIRECT formula).

This INDIRECT formula refers to Cell G4 on every product-sheet. This cell G4 gives me the last value of column E on the same sheet with a LOOKUP formula. On the top of every product-worsheet I added again the name of the product using a MID /FIND formula.   

Now the problem: if I delete a sheet (because we don't have the product anymore for example), the sheet disappears and on the INDEX-sheet in column B the name of the deleted product with the hyperlink is removed (rest of the cells in the column move up) BUT the other columns on the INDEX-sheet stay as they are with a wrong column D and wrong column E as a result. I tried to delete a product directly on the INDEX-sheet but this deletes the product name and hyperlink, giving me a blank cell but the other columns D and E shft one cell up again giving the wrong value for the product.

My question: is there a way to delete a sheet without messing up the values in column D and E on the INDEX sheet?       

Hopefully the explanations are somewhat clear! 

Regards

Tom

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1800
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
December 10, 2022 - 4:43 pm
sp_Permalink sp_Print

Short Answer: No, if you manually type values in Index column D.

Instead, you should type these values in a manual table, where you should type the sheet name in a column and Starting Status in another column.

Your Index Column D should have a formula to get data from the Manual entries table, based on sheet name.

Avatar
Tom VAN LOO

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 27, 2022
sp_UserOfflineSmall Offline
3
December 11, 2022 - 6:52 pm
sp_Permalink sp_Print

Thanks a lot, Catalin.

I did add a Helper-Tab with the manual inputs and a LOOKUP function to get these inputs in the Index table, like you suggested. Now it works just fine to delete a productsheet.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Mynda Treacy, Ivan Kulubya, Duaa Elsheikh, Christian Huapaya, Laxmi Praveen
Guest(s) 16
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1431
Anders Sehlstedt: 845
Velouria: 574
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 213
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 185
Newest Members:
Vicky Otosnika
Abhishek Singh
Kevin Sojourner
Kara Weiss
And Woox
Armani Quenga
moshood bello
annelies b
James1989
lucy gilmour
Forum Stats:
Groups: 3
Forums: 24
Topics: 6045
Posts: 26524

 

Member Stats:
Guest Posters: 49
Members: 31492
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x