Offset for Dynamic Chart Range

ktkelly_1

New Member
Joined
Jun 13, 2019
Messages
17
[FONT=&quot]I am trying to create a dynamic chart range for this specific table but keep getting a #VALUE error. I'm not absolutely sure what it is the issue maybe syntax?
[/FONT]
[FONT=&quot]=OFFSET($E$28,0,0,(COUNTIF(E$28:E34,"<>")),(COUNTIF($E$28:AE34,"<>")))
[/FONT]

DEFGHIJKLMNOPQRS.................................AE
28 SEM - Estimates 000000000000000000000000000
29 SSE - Estimates 000000000000000000000000000
30 Aircraft - Estimates 000000000000000000000000000
31 DSS - Estimates 000000000000000000000000000
32 Portal - Estimates 000000000000000000000000000
33 IVV - Estimate 000000000000000000000000000
34 Prod Sys - Estimates 000000000000000000000000000
35Total000000000000000000000000000
3

<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
[FONT=&quot]I am trying to attempt still get a #VALUE error. When evaluating the formula it seems to compute it until Offset($E$28,0,0,7,(189)) and then it turns into the #VALUE error. Any ideas?[/FONT]
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
It seems to me that this formula would return a #VALUE error for ANY other situation where the final two arguments are not = 1, not just 7 and 189.

For example
=Offset($E$28,0,0,2,2)
also returns a #VALUE error.

If you want to return the value of a single cell defined in the offset function, use
=Offset($E$28,0,0,1,1)

I wonder if you are confusing the arguments in the OFFSET function ?

The first two arguments specify the rows and columns to offset from the start point.
The final two arguments specify the size of the range to return.
If you want to sum all the values within that range, use something like
=SUM(offset . . .

Comment - it's difficult to test your data when it's essentially all zeroes.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top