How to tie rows to columns that update

[realPrincessApril]

Hi all,

I'm not sure how to handle my problem in Sheet C:

Sheet A
Column A is a list of my client caseload. This is a named range (ClientList) and expands and contracts as I open and close cases.

Sheet C
Column A displays the aforementioned ClientList named range from Sheet A. In Columns B, C, etc., I have manually entered several bits of data (medication names, start dates, provider name, etc.). All good so far, but then I open or close a case in Sheet A and sort my list to realphabetize and remove any blank rows. So the named range changes in Sheet A and therefore in Sheet C, Column A. However, all the columns to the right of Sheet C, Column A are static and do not sort along with the new order and values in the named range ClientList.

Problem
Is there a way to tie Sheet C's columns (say B through S) to Sheet C, Column A so that, when the named range updates, those rows also sort as needed to stay tied to the appropriate client? I realize I could put everything into Sheet A, but the information really belongs in separate sheets if possible.

Any guidance is welcome. Thank you for any time and assistance you can provide, and happy holidays to anyone celebrating.

 

[RasGhul]

You would need to make data table for you detailed client information on a new Sheet e.g Sheet D. Then use a lookup to return the detailed information to Sheet C, Sheet C becomes the expanded view of your cases and info. We will need to use an array formula to always return the same of order case names from ClientList.

Are you able to post any sample data for us to look at?

 

[realPrincessApril]

Hi @RasGhul thank you for your reply. I created a simplified and desensitized sample book and uploaded here. If it's unclear just let me know and I can provide more info (I left some notes in there). For now have a nice night and perhaps holiday if in the US and celebrating.

 

[RasGhul]

Maybe you could use this;

Ideally you will need to create 1 data table for all of your client data & changes, then extract to the Sheet A & C tables, then when you sort or delete rows in the data table Sheet A & C auto reflect the changes.

I created a Sheet called data, and a table called ClientData, the following should be ok as long as all user's are on Office 365; Note the the Full name spills from the ClientData table dynamically;

If anyone else uses the sheet with an older excel version we will need an array & formulas to fill data.

How to tie rows to columns that update_Sample book_realprincess.xlsx
	A	B	C	D	E	F	G	H	I	J
1	LAST	FIRST	FULL	DOB	SSN	Certification Exp Date	Assessment Date	MED 1 NAME	MED 1 START DATE	PROVIDER
2	Allen	Kaitlin	Allen, Kaitlin	1/01/1974	blee	cert1	assess1	med1	1/01/2021	provider1
3	Ayala	Genevieve	Ayala, Genevieve	2/01/1974	dah	cert2	assess2	med2	2/01/2021	provider2
4	Blevins	Lana	Blevins, Lana	3/01/1974	2	cert3	assess3	med3	3/01/2021	provider3
5	Bray	Kaylyn	Bray, Kaylyn	5/01/1974	4	cert4	assess4	med5	5/01/2021	provider5
6	Davila	Bentley	Davila, Bentley	6/01/1974	blee	cert5	assess5	med6	6/01/2021	provider6
7	Lynn	Jon	Lynn, Jon	7/01/1974	dah	cert6	assess6	med7	7/01/2021	provider7
8	Martin	Alisson	Martin, Alisson	8/01/1974	2	cert7	assess7	med8	8/01/2021	provider8
9	Riggs	Maximillan	Riggs, Maximillan	9/01/1974	4	cert8	assess8	med9	9/01/2021	provider9
10	Santana	Mya	Santana, Mya	10/01/1974	blee	cert9	assess9	med10	10/01/2021	provider10
11	Ward	Gary	Ward, Gary	11/01/1974	dah	cert10	assess10	med11	11/01/2021	provider11
12	Webster	August-Crystal	Webster, August-Crystal	12/01/1974	2	cert11	assess11	med12	12/01/2021	provider12
Data

How to tie rows to columns that update_Sample book_realprincess.xlsx
	A	B	C	D	E	F
1	LAST	FIRST	FULL	MED 1 NAME	MED 1 START DATE	PROVIDER
2	Allen	Kaitlin	Allen, Kaitlin	med1	44197	provider1
3	Ayala	Genevieve	Ayala, Genevieve	med2	2/01/2021	provider2
4	Blevins	Lana	Blevins, Lana	med3	3/01/2021	provider3
5	Bray	Kaylyn	Bray, Kaylyn	med5	5/01/2021	provider5
6	Davila	Bentley	Davila, Bentley	med6	6/01/2021	provider6
Med Updates

Cell Formulas
Range	Formula
A2:A12	A2	=ClientData[LAST]
B2:B12	B2	=ClientData[FIRST]
C2:C12	C2	=ClientData[FULL]
D2:D12	D2	=ClientData[MED 1 NAME]
E2:E12	E2	=ClientData[MED 1 START DATE]
F2:F12	F2	=ClientData[PROVIDER]
Dynamic array formulas.

How to tie rows to columns that update_Sample book_realprincess.xlsx
	A	B	C	D	E	F	G
6	LAST	FIRST	FULL	DOB	SSN	Certification Exp Date	Assessment Date
7	Allen	Kaitlin	Allen, Kaitlin	1/01/1974	blee	cert1	assess1
8	Ayala	Genevieve	Ayala, Genevieve	2/01/1974	dah	cert2	assess2
9	Blevins	Lana	Blevins, Lana	3/01/1974	2	cert3	assess3
10	Bray	Kaylyn	Bray, Kaylyn	5/01/1974	4	cert4	assess4
11	Davila	Bentley	Davila, Bentley	6/01/1974	blee	cert5	assess5
Client Tracker

Cell Formulas
Range	Formula
A7:A17	A7	=ClientData[LAST]
B7:B17	B7	=ClientData[FIRST]
C7:C17	C7	=ClientData[FULL]
D7:D17	D7	=ClientData[DOB]
E7:E17	E7	=ClientData[SSN]
F7:F17	F7	=ClientData[Certification Exp Date]
G7:G17	G7	=ClientData[Assessment Date]
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8,A7:C7,A8:B14,A21:C47,A16:B20,C8:C20	Expression	=AND(MONTH($E7)=MONTH(TODAY()),DAY($E7)=DAY(TODAY()))	text	NO