Reference Cell Contents in Range Formula

[jski21]

Good day Mr. Excel Team,

Seeking some expert wisdom here. I have a pivot table that is dynamic in range dependent upon what criteria I select. I have a SMALL function formula that then looks into the range to find the bottom 5 observations according to their value:

="1. "&INDEX(Pivots!$A$4:$A$14,MATCH(SMALL(Pivots!$C$4:$C$14,ROWS($B15:$B$15)),Pivots!$C$4:$C$14,0))

Everything works fine except that I find myself having to manually change the end of the range (red 14 above) in each of the five formulas to accommodate the dynamic range of the pivot table. I did find a way to determine the number of the last row of data using the formula =ROW(OFFSET(A1,COUNTA(A:A),0)) and have this in a cell (in this case, "14").

I'm struggling with finding a way to create a formula to accommodate the ROW, OFFSET result into the SMALL function formula so that I don't have to manually change the Pivots! range reference.

Hopefully this is not as unique of a request as I think it is and the Mr. Excel Team can offer a solution.

Thanks in advance for the review, consideration, and assistance.


jski

 

[arun_eagertolearn]

Hi

You may us the indirect formula to integrate both


="1."&INDEX(Pivots!$A$4:indirect("$A$"&ROW(OFFSET(A1,COUNTA(A:A),0))),MATCH(SMALL(Pivots!$C$4:indirect("$C$"&ROW(OFFSET(A1,COUNTA(A:A),0))),ROWS($B15:$B$15)),Pivots!$C$4:indirect("$C$"&ROW(OFFSET(A1,COUNTA(A:A),0))),0))

 

[jski21]

Thanks for the response. Interesting approach but I'm receiving a #VALUE error when I plug it in. Might you be open to taking a look at a sample file? I'm probably over looking something minor.

 

[arun_eagertolearn]

Hi

Can you pls share a sample of the excel sheet

Thanks

 

[jski21]

Hi

Can you pls share a sample of the excel sheet

Thanks

Here's a snapshot. Cell B15 is my original formula. Cell C24 is where I'm attempting your version. I can send you the sample file if you'd like. Just message me with your email. Thank Arun!

Sample File.xlsx
	A	B	C	D	E	F	G	H	I
1						2
2
3
4
5
6
7		Top 5 Funded Projects (2019 - 2023)
8		1. Harvard Comm. Svcs. Ctr. - CDC Activity Grant					$1,056,172.41
9		2. Famicos Foundation - CDC Activity Grant					$757,336.38
10		3. Union Miles Dev. Corp. - CDC Activity Grant					$114,771.55
11		4. Bryce Avenue Street Resurfacing					$76,190.00
12		5. Ohio City/Near West Recreation (Swap with Mayor's Office)					$49,506.39
13
14		Bottom 5 Funded Projects (2019 - 2023)
15		1. Greater CLE Volunteers - AARP Fdn Literacy Tutoring					$1,000.00
16		2. Mount Pleasant NOW - CDC Activity Grant					$1,053.84
17		3. Center for Arts - Inspired Learning: ArtWorks					$1,984.96
18		4. First Street Coalition - Mt. Pleasant INFO Magazine					$9,000.00
19		5. Comm. Neighborhood Organizer - Harvard Comm. Svcs. Ctr.					$40,000.00
20
21
22
23
24			#VALUE!
25
Charts

Cell Formulas
Range	Formula
B8	B8	="1. "&INDEX(Pivots!$A$4:$A$30,MATCH(LARGE(Pivots!$C$4:$C$30,ROWS($B$8:$B$8)),Pivots!$C$4:$C$30,0))
B9	B9	="2. "&INDEX(Pivots!$A$4:$A$30,MATCH(LARGE(Pivots!$C$4:$C$30,ROWS($B$8:$B$9)),Pivots!$C$4:$C$30,0))
B10	B10	="3. "&INDEX(Pivots!$A$4:$A$30,MATCH(LARGE(Pivots!$C$4:$C$30,ROWS($B$8:$B$10)),Pivots!$C$4:$C$30,0))
B11	B11	="4. "&INDEX(Pivots!$A$4:$A$30,MATCH(LARGE(Pivots!$C$4:$C$30,ROWS($B$8:$B$11)),Pivots!$C$4:$C$30,0))
B12	B12	="5. "&INDEX(Pivots!$A$4:$A$30,MATCH(LARGE(Pivots!$C$4:$C$30,ROWS($B$8:$B$12)),Pivots!$C$4:$C$30,0))
G8:G12	G8	=LARGE(Pivots!$A$4:$B$14,ROWS($B$8:$B8))
B15	B15	="1. "&INDEX(Pivots!$A$4:$A$14,MATCH(SMALL(Pivots!$C$4:$C$14,ROWS($B15:$B$15)),Pivots!$C$4:$C$14,0))
B16	B16	="2. "&INDEX(Pivots!$A$4:$A$14,MATCH(SMALL(Pivots!$C$4:$C$14,ROWS($B15:$B$16)),Pivots!$C$4:$C$14,0))
B17	B17	="3. "&INDEX(Pivots!$A$4:$A$14,MATCH(SMALL(Pivots!$C$4:$C$14,ROWS($B15:$B$17)),Pivots!$C$4:$C$14,0))
B18	B18	="4. "&INDEX(Pivots!$A$4:$A$14,MATCH(SMALL(Pivots!$C$4:$C$14,ROWS($B15:$B$18)),Pivots!$C$4:$C$14,0))
B19	B19	="5. "&INDEX(Pivots!$A$4:$A$14,MATCH(SMALL(Pivots!$C$4:$C$14,ROWS($B15:$B$19)),Pivots!$C$4:$C$14,0))
G15	G15	=SMALL(Pivots!$A$4:$B$14,ROWS($B15:$B$15))
G16	G16	=SMALL(Pivots!$A$4:$B$14,ROWS($B15:$B$16))
G17	G17	=SMALL(Pivots!$A$4:$B$14,ROWS($B15:$B$17))
G18	G18	=SMALL(Pivots!$A$4:$B$14,ROWS($B15:$B$18))
G19	G19	=SMALL(Pivots!$A$4:$B$14,ROWS($B15:$B$19))
C24	C24	="1."&INDEX(Pivots!$A$4:INDIRECT("$A$"&ROW(OFFSET(Pivots!A1,COUNTA(Pivots!A:A),0))),MATCH(SMALL(Pivots!$C$4:INDIRECT("$C$"&ROW(OFFSET(Pivots!A1,COUNTA(Pivots!A:A),0))),ROWS($B15:$B$15)),Pivots!$C$4:INDIRECT("$C$"&ROW(OFFSET(Pivots!A1,COUNTA(Pivots!A:A),0))),0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B19	Cell Value	duplicates	text	NO
B12	Cell Value	duplicates	text	NO
B8:B11	Cell Value	duplicates	text	NO
B15:B18	Cell Value	duplicates	text	NO

 

[arun_eagertolearn]

Thanks for sharing
Since the formulae refer to 'Pivots' sheet, that is what I require

 

[jski21]

Good day,,

Here is an XL2BB snapshot of the Pivots sheet:

Sample File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Type of Activity	(Multiple Items)			Type of Activity	(Multiple Items)						15
2
3	Project	Individual Commitment	Adjust for Dupes		Year	Amount			Type	Amount
4	CLE Restoration Society - Heritage Home Prgm	46,000.00	46,000.00		2019	45,000			CDC	1,929,334.18
5	Greater CLE Volunteers - AARP Fdn Literacy Tutoring	1,000.00	1,000.00		2020	690,000			CW	46,000.00
6	Harvard Comm. Svcs. Ctr. - CDC Activity Grant	1,056,172.41	1,056,172.41		2021	421,626			SS	90,928.35
7	Union Miles Dev. Corp. - CDC Activity Grant	114,771.55	114,771.55		2022	527,812			PS	1,984.96
8	Famicos Foundation - CDC Activity Grant	757,336.38	757,336.38		2023	500,000			Planning	40,000.00
9	Comm. Neighborhood Organizer - Harvard Comm. Svcs. Ctr.	40,000.00	40,000.00		Grand Total	2,184,438			Str. Imprvmnt	76,190.00
10	Bryce Avenue Street Resurfacing	76,190.00	76,190.00						Grand Total	2,184,437.49
11	Center for Arts - Inspired Learning: ArtWorks	1,984.96	1,984.96
12	First Street Coalition - Mt. Pleasant INFO Magazine	9,000.00	9,000.00
13	Mount Pleasant NOW - CDC Activity Grant	1,053.84	1,053.84
14	Ohio City/Near West Recreation (Swap with Mayor's Office)	49,506.39	49,506.39
15	Harvard Comm. Svcs. Ctr. - Beautification and Litter Clean-up	31,421.96	31,421.96
16			(0.02)
17			(0.02)
18			(0.02)
19			(0.02)
20			(0.02)
21			(0.02)
22			(0.02)
23			(0.02)
24			(0.02)
25			(0.02)
26			(0.02)
27			(0.02)
28			(0.02)
29			(0.02)
30			(0.02)
Pivots

Cell Formulas
Range	Formula
L1	L1	=ROW(OFFSET(A1,COUNTA(A:A),0))
C4:C30	C4	=B4+(COUNTIF($B$4:B4,B4)-1)*0.015

I'm not seeing an option to upload a file on this platform. If the sample file would be useful, please provide your email address and I can send it.

Thanks!

 

[arun_eagertolearn]

Hi

Since the reference is to the 'Pivots' sheet, the formula will have to be modified as below

="1."&INDEX(Pivots!$A$4:INDIRECT("Pivots!$A$"&ROW(OFFSET(Pivots!A1,COUNTA(Pivots!A:A),0))),MATCH(SMALL(Pivots!$C$4:INDIRECT("Pivots!$C$"&ROW(OFFSET(Pivots!A1,COUNTA(Pivots!A:A),1))),ROWS($B15:$B$15)),Pivots!$C$4:INDIRECT("Pivots!$C$"&ROW(OFFSET(Pivots!A1,COUNTA(Pivots!A:A),0))),0))

 

[jski21]

Outstanding Arun. Thanks so much for your time and assist with this. Huge time saver for me and very instructive as well!


jski