jenniferRI
New Member
- Joined
- Jan 11, 2022
- Messages
- 9
- Office Version
- 2016
- Platform
- Windows
I have several tables in several sheets, they have in common several columns but each table has a different number of columns. I used formulas to copy the shared data and I fill the othe data manually.
My objectives are the following:
1) Each time I change the value of the included in registry field in overview sheet, I want to automatically reorder all the tables accordingly (put all included patients first) and also according to the value precised in the drop down box.
2) Use the sort button to sort all the data according to the value of the drop down box.
for the first objective I was able to reorder the data automatically in the overview sheet, so in this case the common columns in the other sheets are ordered however the data I filled manually don't change their order. and I did it without taking into consideration the value of the drop down box (I chose patient ID in the macro code)
for the second objective I couldn't do it
Thank you so much for you help,
I am puting here only 3 sheets to understand more what I have
Overview sheet:
Base Sheet:
Medication sheet:
My objectives are the following:
1) Each time I change the value of the included in registry field in overview sheet, I want to automatically reorder all the tables accordingly (put all included patients first) and also according to the value precised in the drop down box.
2) Use the sort button to sort all the data according to the value of the drop down box.
for the first objective I was able to reorder the data automatically in the overview sheet, so in this case the common columns in the other sheets are ordered however the data I filled manually don't change their order. and I did it without taking into consideration the value of the drop down box (I chose patient ID in the macro code)
for the second objective I couldn't do it
Thank you so much for you help,
I am puting here only 3 sheets to understand more what I have
Overview sheet:
registry_test.xlsm | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
2 | ||||||||||||||||||||
3 | Patient ID | Registry ID | Patient Initials | Date Of Birth | Sex | Included in Registry | Informed Consent | Center | Date ABPM Inclusion | Base Completed | Medical History Completed | Medication Completed | Office BP Completed | ABPM Completed | Laboratory Results Completed | Follow Up Completed | ||||
4 | 16942 | 10 | LM | 10/18/1970 | F | Y | 12/28/2017 | Y | Y | Y | N | N | Y | N | ||||||
5 | 16943 | 100 | RA | 10/1/1979 | M | Y | 9/8/2020 | N | Y | 0 | 0 | 0 | 0 | 0 | ||||||
6 | 16947 | 105 | RN | 10/18/1970 | F | Y | 12/28/2017 | N | N | Y | N | Y | Y | Y | ||||||
7 | 16948 | 108 | SA | 5/20/1979 | F | Y | 3/30/2019 | Y | 0 | 0 | 0 | 0 | 0 | 0 | ||||||
8 | 16945 | 102 | RB | 12/14/1965 | M | N | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
9 | 16950 | 110 | SM | 3/9/1979 | M | N | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
10 | 16955 | 115 | ZD | 10/10/1972 | F | N | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
Overview |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J4:J10 | J4 | =IFERROR(VLOOKUP([@[Patient ID]],Table2,12,FALSE),"") |
K4:K10 | K4 | =IFERROR(VLOOKUP([@[Patient ID]],Table3,36,FALSE),"") |
L4:L10 | L4 | =IFERROR(VLOOKUP([@[Patient ID]],Table4,26,FALSE),"") |
M4:M10 | M4 | =IFERROR(VLOOKUP([@[Patient ID]],Table6,19,FALSE),"") |
N4:N10 | N4 | =IFERROR(VLOOKUP([@[Patient ID]],Table7,13,FALSE),"") |
O4:O10 | O4 | =IFERROR(VLOOKUP([@[Patient ID]],Table8,34,FALSE),"") |
P4:P10 | P4 | =IFERROR(VLOOKUP([@[Patient ID]],Table9,29,FALSE),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A4:P300 | Expression | =$F4="N" | text | NO |
J:P | Cell Value | ="N" | text | NO |
J:P | Cell Value | ="Y" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F4:G10 | List | Y,N |
Base Sheet:
registry_test.xlsm | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
3 | Patient ID | Registry ID | Patient Initials | Date Of Birth | Sex | Age at time of measurement | Ethnicity | Weight (kg) | Height (cm) | BMI (kg/m²) | Body Surface Area DuBois (m²) | Base Completed | |||
4 | 16942 | 10 | LM | 10/18/1970 | F | 47 | Other | 58 | 149 | 26.12495 | 1.51837 | Y | |||
5 | 16943 | 100 | RA | 10/1/1979 | M | 41 | 51 | 155 | 21.22789 | 1.479342 | N | ||||
6 | 16947 | 105 | RN | 10/18/1970 | F | 47 | 78 | 168 | 27.63605 | 1.878668 | N | ||||
7 | 16948 | 108 | SA | 5/20/1979 | F | 40 | 63 | 172 | 21.29529 | 1.745173 | Y | ||||
8 | 16945 | 102 | RB | 12/14/1965 | M | 0 | |||||||||
9 | 16950 | 110 | SM | 3/9/1979 | M | 0 | |||||||||
10 | 16955 | 115 | ZD | 10/10/1972 | F | 0 | |||||||||
Base |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A4:A10 | A4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,1,FALSE),"") |
B4:B10 | B4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,2,FALSE),"") |
C4:C10 | C4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,3,FALSE),"") |
D4:D10 | D4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,4,FALSE),"") |
E4:E10 | E4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,5,FALSE),"") |
F4:F10 | F4 | =IF(YEAR(Table1[@[Date ABPM Inclusion]])-YEAR(Table1[@[Date Of Birth]])<0,"",YEAR(Table1[@[Date ABPM Inclusion]])-YEAR(Table1[@[Date Of Birth]])) |
J4:J10 | J4 | =IFERROR([@[Weight (kg)]]/(([@[Height (cm)]]/100)*([@[Height (cm)]]/100)),"") |
K4:K10 | K4 | =IFERROR(0.20247*([@[Height (cm)]]/100)^0.725*[@[Weight (kg)]]^0.425,"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A4:L300 | Expression | =Overview!$F4="N" | text | NO |
K1:K2,K301:K1048576,L3:L300 | Cell Value | ="N" | text | NO |
K1:K2,K301:K1048576,L3:L300 | Cell Value | ="Y" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
L4:L10 | List | Y, N |
G4:G10 | List | African, Asian, Caucasian, Hispanic, Other, Unknown |
Medication sheet:
registry_test.xlsm | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |||
3 | Patient ID | Registry ID | Patient Initials | Date Of Birth | Sex | Current use of antihypertensive drugs | Diuretics | Diuretics Loop | Diurectis Thiazide | Diuretics Potassium-sparing | Centrally acting | CCB - dih | CCB - non-dih | ACE Inhibitors | ARB | LCZ696 | Vasodilators | Renin inhibitors | Alpha blockers | Beta blockers | Statins | Nitrovasodilators | *** | Oral Anticoagulants | Other Antithrombotic drugs | Medication Complete | |||
4 | 16942 | 10 | LM | 10/18/1970 | F | Y | |||||||||||||||||||||||
5 | 16943 | 100 | RA | 10/1/1979 | M | ||||||||||||||||||||||||
6 | 16947 | 105 | RN | 10/18/1970 | F | Y | |||||||||||||||||||||||
7 | 16948 | 108 | SA | 5/20/1979 | F | ||||||||||||||||||||||||
8 | 16945 | 102 | RB | 12/14/1965 | M | ||||||||||||||||||||||||
9 | 16950 | 110 | SM | 3/9/1979 | M | ||||||||||||||||||||||||
10 | 16955 | 115 | ZD | 10/10/1972 | F | ||||||||||||||||||||||||
Medication |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A4:A10 | A4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,1,FALSE),"") |
B4:B10 | B4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,2,FALSE),"") |
C4:C10 | C4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,3,FALSE),"") |
D4:D10 | D4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,4,FALSE),"") |
E4:E10 | E4 | =IFERROR(VLOOKUP(Table1[Patient ID],Table1,5,FALSE),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A4:Z300 | Expression | =Overview!$F4="N" | text | NO |
Z:Z | Cell Value | ="N" | text | NO |
Z:Z | Cell Value | ="Y" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F4:F300 | List | Y, N |
G4:Y300 | List | Y, N, UNK |
Z4:Z300 | List | Y, N |