+ Reply to Thread
Results 1 to 4 of 4

Data Validation- Formula Automatically Shifting Referenced Column

  1. #1
    Registered User
    Join Date
    03-20-2020
    Location
    Maywood, USA
    MS-Off Ver
    2016 & 365
    Posts
    3

    Data Validation- Formula Automatically Shifting Referenced Column

    Greetings!

    Backstory:So I made this workbook to make it easier for Supply Chain of our hospital to process all items used in a patient's hospital case by making an excel sheet with an interactive page vs what was used previously (see the tab "Data"). I managed to convert what they currently use to a more user friendly format, "SSID INFO."


    The Issue: the data validation formula for cell B5 changes every time you move tabs. It changes the formula range to reference the next column when you return back to the tab. The result is that it goes from from CASE #[X]-[LOG No.] to [corresponding number]. (e.g Case #1-906306 -> 1). I am unsure what exactly is causing this to happen. Unfortunately, my knowledge in data validation is limited to having people fill out forms, referencing data from a static range, and not extending beyond one tab.


    How It Works: For simplicity, I removed the macro that converts the raw extracted data and pastes it appropriately into the tab, "DATA." I also removed the macro to refresh the pivot tables. In tab, "SSID INFO," I have a pivot table that starts in cell S1 that references info in the tab, "DATA." Column T references the numerical order next to the pivot table. Cell U1 has a vlookup that references which case number the sheet is currently referencing. Cell V1 has the max of range, $T:$T.

    The data validation is in Cell B5. All the vlookups and the pivot table in Q19 are powered by what appears in cell b5. The pivot table's filter matches whatever is in cell B5. Data validation is referencing the entire column and is using offset to remove blank cells, since the pivot table is dynamic due to more information being added throughout the month.

    The data tab: the info below the purple header is automatically generated (entered fake data), the black header was added by me for reference purposes, and the blue references an external report (formulas have been removed to avoid external reference error).

    VBA: 1) cmd_nxt (SSID INFO) - allows the buttons to select the next case or go back to the previous case. (See "PREVIOUS" & "NEXT")
    2) module 1 - creates the function removenumbers
    3) module 2 - creates the function no_brackets
    4) This worbook - Automatically changes the filter for the pivot table in Q19 to match Cell B5.


    Any assistance would be greatly appreciated!
    Attached Files Attached Files
    Last edited by Ekazumon; 03-26-2020 at 11:57 AM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,444

    Re: Data Validation- Formula Automatically Shifting Referenced Column

    Hi there,

    Regarding:


    It changes the formula range to reference the next column when you return back to the tab. The result is that it goes from from CASE #[X]-[LOG No.] to [corresponding number]. (e.g Case #1-906306 -> 1). I am unsure what exactly is causing this to happen.

    "what exactly is causing this to happen" is the following routine:

    Please Login or Register  to view this content.
    which is located in the VBA CodeModule of the "SSID INFO" worksheet.


    Not knowing the background to your application I have no idea of why this changing of data validation criteria was once / is still necessary, but if you want to overcome your immediate problem you could delete or "comment-out" this routine and see if the application works correctly afterwards.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    03-20-2020
    Location
    Maywood, USA
    MS-Off Ver
    2016 & 365
    Posts
    3

    Re: Data Validation- Formula Automatically Shifting Referenced Column

    Dear Greg,

    Ah. Figured out the problem. It was supposed to be referencing Column S not T. In hindsight I am a bit of a dummy. I deleted a column and forgot to change that part of code. I guess I was staring at the thing for so long that I didn't notice it.

    Thank you for the help!


    Sincerely,

    Ekazumon

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,444

    Re: Data Validation- Formula Automatically Shifting Referenced Column

    Hi again,

    Many thanks for your feedback.

    You're welcome - glad I was able to help.

    For information, the use of named ranges instead of Row/Column references (e.g. ptrExpenses rather than B5:Q23) is much more robust, and should normally be used whenever there is a possibility of worksheet layout changes.

    Regards,

    Greg M

+ 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. shifting data into another column when condition met in a loop
    By barrowinfurnace in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2016, 11:22 AM
  2. [SOLVED] shifting data to new column
    By kochark in forum Excel General
    Replies: 7
    Last Post: 11-27-2014, 03:36 PM
  3. Working Validation No Longer Works After Shifting to another Column for the Source of Data
    By young_padawan_in_exl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-16-2013, 08:16 AM
  4. Shifting rows of data so that they all line up in the same column
    By Ricker090 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2012, 04:56 PM
  5. Shifting an AVERAGE formula down automatically
    By Iron Mike in forum Excel General
    Replies: 5
    Last Post: 06-21-2011, 12:09 PM
  6. [SOLVED] Automatically shifting data, part II
    By S. Stone in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-05-2005, 05:06 PM
  7. Shifting text on column B to column A automatically
    By leasaac in forum Excel General
    Replies: 2
    Last Post: 01-27-2005, 11:02 PM

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