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!
Bookmarks