+ Reply to Thread
Results 1 to 2 of 2

Using tab titles in formula

  1. #1
    Registered User
    Join Date
    03-07-2019
    Location
    Dublin, Ireland
    MS-Off Ver
    365 Suite
    Posts
    1

    Using tab titles in formula

    Hi!

    New to the forum. I've been using Excel for a long time, lotus 123 before that, but have only a very shallow understanding of it. I have a large excel sheet with tabs for each week, it's used for my business to monitor weekly financial activities. It has turnover & profit information per client on each tab. What I want to do is have a separate tab that monitors which clients we are currently working with as a way to isolate with who we're having a downturn and redouble efforts to chase them up. It would also be handy to be able to tell when we last worked with them. Basic look of the sheet is below, I'd like the formula in the "current" column to search through the tab referenced by the cell adjacent to "week ending date" (set to 10/03/2019 for this example) and check if the client name in the "name" column appears. The client names are all in the same column on each tab, the tabs of each week are a copy of each other just with the raw data changed. If so, it'll return "Yes" and if not "no". Then, if the "current" column is returned as "no" the formula in the "Last interaction" column will then go through the other tabs (max 52 for a year) until it finds said client and inserts the date from that tab in the cell. Is this at all possible or am I totally dreaming? I obviously am unable to send anyone the actual excel sheet as it contains a large amount of personal data for my candidates and clients but please ask if you have any questions that will help resolve.

    Many thanks,

    Lewis


    Week ending date 10/03/2019

    Name Current Last interaction
    Client 1 Yes Current
    Client 2 Yes Current
    Client 3 No 17/02/2019

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Using tab titles in formula

    Hello kingalew2019 and Welcome to Excel Forum.
    As you have the subscription version of Excel, you could use Get & Transform to produce a table that includes all of the weekly tables and then use formulas to get the most recent table containing the client.
    On the week sheets the data is put in table format,
    Use the following information, credit Mynda Treacy, to produce the table on the AllWeeks sheet:
    1. You can use the Excel.CurrentWorkbook function to do this.
    2. Create a blank query in your Excel file that contains the tables you want to combine. In the Query Editor formula bar type: =Excel.CurrentWorkbook()
    3. Press ENTER
    4. This will bring up a list of all the tables, sheets and named ranges in your file. Filter the Name column to select the items you want.
    Tip: name all of your tables with a common beginning or ending. e.g. I start the name of all my tables with 'tbl_...' so when I filter the list I can simply specify Text that Begins With; tbl_ and then when I add any new tables they will be included in the query.
    On the summary sheet the 'Current' column is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The 'Last Interaction' column is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If this will not work for your situation you may edit the file and upload by clicking on the GO ADVANCED button below the quick reply window and then scrolling down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to keep titles, sub-titles, and blank space when sorting
    By wanttolearnexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2018, 12:54 AM
  2. [SOLVED] Programming a formula to abbreviate text titles
    By highlystrung in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2013, 03:01 AM
  3. Replies: 4
    Last Post: 06-22-2011, 09:50 AM
  4. Replies: 4
    Last Post: 03-08-2011, 12:44 PM
  5. Formulas in Chart Titles & Axis Titles
    By Bobbbo in forum Excel General
    Replies: 1
    Last Post: 05-20-2010, 02:02 PM
  6. Pivot formula-list of song titles
    By poconnor in forum Excel General
    Replies: 2
    Last Post: 04-10-2007, 11:48 PM
  7. [SOLVED] Titles
    By Troy in forum Excel General
    Replies: 4
    Last Post: 01-31-2006, 10:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1