Use data from cells of one worksheet table to determine table & column selection in a different worksheet.

[Worksong]

Hello all! I'm not sure what I'm after can be done, but figured I'd ask anyway. I'm setting up a spreadsheet to score a 66-question assessment that rates 8 categories of data.
The raw score for each category corresponds with a T-score (kind of like a percentile) that is adjusted for age range and gender. The paper version has two very large tables, one each for male and female respondents, that has 8 columns (one for each category), each of which has 4 sub-columns (one for each age range). To score, one picks the appropriate respondent gender table, category, and column for the respondent's age, and then finds the correct T-score for the raw score. With me so far?

In my spreadsheet so far, there are cells to enter the respondent's age and gender.

The spreadsheet takes the 66 answers and calculates a raw score for each of the 8 categories and puts it in one of two tables, one for male respondents and one for female respondents. Each table has four columns that represent age ranges.

I set up separate tabs for male and female respondents, and each tab has a table for each age range. See screenshots below:

I want the spreadsheet to use the age, gender and raw score cells for each category to select the correct tab, table and column, match the raw score to the appropriate T-score, and populate the T-score in the appropriate cell of the raw score tables of the main spreadsheet. Still with me?

For a 34 year-old female respondent, I want the spreadsheet to select these raw scores:

Select this tab:

This table:

And then use each category's raw score to select the corresponding T-score and return it to the main spreadsheet:

If Excel can in fact do something like this, any help is appreciated. Thank you!!

 

[Vogateer]

Sounds like you want a dynamic table reference. How do you have the tables named? If you have consistently named tables for those T scores, I think you could use INDIRECT to create a table reference. For example, if you name your tables "[gender]_[age range]", then I think that makes this a lot easier.

INDIRECT is a volatile function that can affect performance, but in this case I think it's either that or figuring out how to combine all your T scores into one big table.

 

[Worksong]

Sounds like you want a dynamic table reference. How do you have the tables named? If you have consistently named tables for those T scores, I think you could use INDIRECT to create a table reference. For example, if you name your tables "[gender]_[age range]", then I think that makes this a lot easier.

INDIRECT is a volatile function that can affect performance, but in this case I think it's either that or figuring out how to combine all your T scores into one big table.

Thanks Vogateer. Er, I wasn't aware that I can choose table names! It wouldn't be very difficult to make one large table - sounds like that's the simplest option?

 

[Vogateer]

Yes, making one large table should be the simplest option. I think you could use VSTACK or some other feature to combine all the tables into one big table, then you should be in good shape to reference it. You might need an identifier column so you can select the values you want, but once that's in place, you're in great shape for this.

 

[HighAndWilder]

Can you explain where all of the data comes from in the first place before the separated tabs are populated?

Is this a one off exercise or does it need to be repeated over time?

 

[Worksong]

Can you explain where all of the data comes from in the first place before the separated tabs are populated?

Is this a one off exercise or does it need to be repeated over time?

Hello HighAndWilder,
Yes - the data is entered by hand, transcribed from paper or PDF. It's a one-off thing - it's a behavior report filled out by an outside observer of a client being evaluated for ADHD. Usually it's filled out by the client's partner, close friend, or parent/close family member. The scoring protocol assesses how consistent the observed behaviors are with adult ADHD, adjusted for the age and gender of the client being evaluated. The assessment is only done once, during the initial evaluation. Does that all make sense?

 

[HighAndWilder]

Leaving any analysis aside for now, the first task is to design the structure of the table into which ALL of the raw data is to be deposited.
Use meaningful column headings and only have one heading row. If multiple tabs or header rows were deemed necessary to describe what the data represents then separate columns will be needed to contain the identifying data.

When designing data structures, one should be aiming for a long thin table and not a short fat one. This will make any subsequent analysis easier and more straight forward.

This does not need to be converted to a table until all of the data has been copied into the same sheet.
It may be easier if it isn't.

When you combine data from different tables you will need columns with repeating data that identifies what the
data represents. In your case, for example and not conclusive, a column for Gender (containing M.F etc) and a column for
Age Range (containing the age range)

Can you use XL2BB to submit some representative data as it was transcribed from paper or PDF. Doing this will mean that
I can create a worksheet out of it. I cannot take data from an image.

Once the entered data is all in one table then we can look at the analysis. Some of the original questions about identifying
which table the data comes from will then be irrelevant.

 

[Worksong]

Leaving any analysis aside for now, the first task is to design the structure of the table into which ALL of the raw data is to be deposited.
Use meaningful column headings and only have one heading row. If multiple tabs or header rows were deemed necessary to describe what the data represents then separate columns will be needed to contain the identifying data.

When designing data structures, one should be aiming for a long thin table and not a short fat one. This will make any subsequent analysis easier and more straight forward.

This does not need to be converted to a table until all of the data has been copied into the same sheet.
It may be easier if it isn't.

When you combine data from different tables you will need columns with repeating data that identifies what the
data represents. In your case, for example and not conclusive, a column for Gender (containing M.F etc) and a column for
Age Range (containing the age range)

Can you use XL2BB to submit some representative data as it was transcribed from paper or PDF. Doing this will mean that
I can create a worksheet out of it. I cannot take data from an image.

Once the entered data is all in one table then we can look at the analysis. Some of the original questions about identifying
which table the data comes from will then be irrelevant.

Hi HighAndWilder, I was able to quickly create one table, just by the nature of the data it's a bit on the "longer and thinner" side - 65 rows / 73 columns.

Here's a close-up of column headers:

 

[HighAndWilder]

Hi HighAndWilder, I was able to quickly create one table, just by the nature of the data it's a bit on the "longer and thinner" side - 65 rows / 73 columns.
View attachment 111452

Here's a close-up of column headers:
View attachment 111453

Hi HighAndWilder, I was able to quickly create one table, just by the nature of the data it's a bit on the "longer and thinner" side - 65 rows / 73 columns.
View attachment 111452

Here's a close-up of column headers:
View attachment 111453

Please read this page from the link below:
XL2BB

XL2BB captures the cells in the selected range and converts it to a special BB code hash that you can paste into a post. The resulting mini-sheet can be copied and pasted back to an Excel worksheet with formulas and formatting that provides a simple way to exchange sample range between the post and Excel application.

Use the XL2BB download and install option on the right hand side of the Mr Excel editor ribbon.

Once you have installed it post the data (63 * 75 columns) using it or come back for more help.

 

[Worksong]

Please read this page from the link below:
XL2BB

XL2BB captures the cells in the selected range and converts it to a special BB code hash that you can paste into a post. The resulting mini-sheet can be copied and pasted back to an Excel worksheet with formulas and formatting that provides a simple way to exchange sample range between the post and Excel application.

Use the XL2BB download and install option on the right hand side of the Mr Excel editor ribbon.

Once you have installed it post the data (63 * 75 columns) using it or come back for more help.

Hi again, the data set was too big for one MiniTable (apparently 3000 cells is the limit and the table has about 4300), so I grabbed it in two pieces.

CAARS-O.L T Score Table Scoring Spreadsheet Draft 051524.xlsm
	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	AB	AC	AD	AE	AF	AG	AH
4	T	F1 18-29								F2 30-39								F3 40-49								F4 50 and Older
5	T1	F1A	F1B	F1C	F1D	F1E	F1F	F1G	F1H	F2A	F2B	F2C	F2D	F2E	F2F	F2G	F2H	F3A	F3B	F3C	F3D	F3E	F3F	F3G	F3H	F4A	F4B	F4C	F4D	F4E	F4F	F4G	F4H
6					F1 - 18-29								F2 - 30-39								F3 - 40-49								F4 - 50 and older
7	90						27	51+	33+	32+					27	51+	33+	31+	36			27	25+	47+	31+	32+				27	25+	47+	31+
8	89						26	50	32						26	50	32	30	35			26	24	46						26	24	46	30
9	88	36						49		31						49								45	30	31						45
10	87						25	48	31	30					25	48	31	29	34			25	23							25	23		29
11	86	35						47								47	30	28	33					44	29	30	36	36				44	28
12	85	34	36			27	24	46	30	29				27	24	46						24	22	43	28	29	35	35		24	22	43
13	84	33						45								45	29	27	32					42				34				42	27
14	83		35			26	23	44	29	28	36	36		26	23	44			31			23	21	41	27	28	34			23	21	41
15	82	32	34			25		43	28	27	35	35		25		43	28	26		36				40		27	33	33	18			40	26
16	81	31		36			22	42							22	42	27		30	35		22	20	39	26		32	32		22	20	39
17	80		33			24		41	27	26	34	34		24		41		25	29	34	18			38	25	26		31				38	25
18	79	30	32	35	18		21	40			33	33	18		21	40	26	24		33		21	19				31		17	21	19
19	78	29	31	34		23			26	25	32	32		23					28		17			37	24	25	30	30				37	24
20	77			33			20	39		24					20	39	25	23	27	32		20	18	36		24	29	29	16	20	18	36
21	76	28	30		17	22		38	25		31	31	17	22		38	24			31				35	23			28				35	23
22	75	27	29	32			19	37	24	23	30	30			19	37		22	26	30	16	19	17	34		23	28		15	19	17	34	22
23	74	26		31	16	21		36			29	29	16	21		36	23		25					33	22		27	27				33
24	73		28	30			18	35	23	22	28	28			18	35		21		29	15	18	16	32	21	22		26		18	16	32	21
25	72	25	27	29	15	20		34		21			15	20		34	22	20	24	28				31		21	26	25	14			31
26	71	24					17	33	22		27	27			17	33			23	27	14	17	15		20		25			17	15		20
27	70		26	28		19		32		20	26	26		19		32	21	19		26				30		20	24	24	13			30
28	69	23	25	27	14	18	16	31	21		25	25	14	18	16	31	20		22			16	14	29	19			23		16	14	29	19
29	68	22	24	26				30	20	19						30		18	21	25	13			28		19	23					28
30	67			25	13	17	15	29		18	24	24	13	17	15	29	19	17		24		15		27	18	18	22	22	12	15		27	18
31	66	21	23					28	19		23	23				28			20	23	12	14	13	26	17		21	21		14	13	26	17
32	65	20	22	24	12	16		27		17	22	22	12	16		27	18	16	19	22				25		17		20	11			25
33	64	19		23			14		18		21	21			14		17					13	12	24	16	16	20			13	12	24	16
34	63		21	22		15		26		16				15		26		15	18	21	11						19	19	10
35	62	18	20		11		13	25	17	15	20	20	11		13	25	16	14	17	20		12	11	23	15	15	18	18		12	11	23	15
36	61	17		21		14		24	16		19	19		14		24				19	10			22	14			17				22
37	60		19	20	10		12	23		14	18	18	10		12	23	15	13	16	18		11	10	21		14	17		9	11	10	21	14
38	59	16	18	19		13		22	15					13		22	14		15		9			20	13	13	16	16				20
39	58	15	17	18	9		11	21		13	17	17	9		11	21		12		17		10	9	19				15	8	10	9	19	13
40	57	14				12		20	14	12	16	16		12		20	13	11	14	16				18	12	12	15	14				18	12
41	56		16	17			10	19	13		15	15	8		10	19			13	15	8	9	8				14		7	9	8
42	55	13	15	16	8	11		18		11		14		11		18	12	10						17	11	11	13	13				17	11
43	54	12		15		10	9	17	12	10	14		7	10	9	17	11		12	14	7	8		16	10	10		12		8		16
44	53		14	14	7			16			13	13				16		9	11	13			7	15			12	11	6		7	15	10
45	52	11	13			9	8	15	11	9	12	12		9	8	15	10			12		7		14	9	9	11			7		14
46	51	10		13	6			14			11	11	6			14		8	10	11	6		6	13			10	10	5		6	13	9
47	50	9	12	12		8	7		10	8		10		8	7		9	7	9			6		12	8	8		9		6		12
48	49	9	11	11	5			13	9	7	10		5			13				10	5		5	11	7	7	9	8	4		5	11	8
49	48	8	10			7	6	12			9	9		7	6	12	8	6	8	9		5					8			5
50	47	7		10				11	8	6	8	8	4			11	7		7	8	4		4	10	6	6		7			4	10	7
51	46	6	9	9	4	6	5	10				7		6	5	10		5		7		4		9		5	7	6	3	4		9	6
52	45	6	8	8				9	7	5	7		3			9	6	4	6				3	8	5		6	5			3	8
53	44	5		7	3	5	4	8		4	6	6		5	4	8			5	6	3	3		7		4	5		2	3		7	5
54	43	4	7					7	6		5	5				7	5	3		5		2	2	6	4	3		4		2	2	6
55	42	4	6	6	2	4	3	6	5	3		4	2	4	3	6	4		4	4	2	1		5	3		4	3	1	1		5	4
56	41	3		5				5			4	3	1			5		2	3	3			1	4		2	3	2			1	4
57	40	2	5	4	1	3	2	4	4	2	3			3	2	4	3				1	0		3	2	1	2	1	0	0		3	3
58	39		4	3			1	3		1	2	2	0		1	3		1	2	2			0	2	1		1	0			0	2
59	38	1	3		0	2	0	2	3		1	1		2	0	2	2	0	1	1	0			1		0	0					1	2
60	37	0		2		1		1		0	0	0		1		1	1		0	0				0	0							0	1
61	36		2	1			0	0	2						0	0	0																0
62	35		1	0		0			1					0
63	34		0						0
64	33
65	32
66	31
67	30
CAARS-OL T scores Table