asked on

I need an OFFSET formula

I need help using the OFFSET formula to auto update the graph information in Excel. I have tried using the following formula, but keep getting errors:

=OFFSET(Patient Info!$A$13,0,0,COUNTA(Patient Info!$A:$A)-1)

I need the graph on the patient info tab to automatically update the graph when new data is entered in A13: B37.

I tried using the embedded table route, but ran into problems when a line had to be deleted because of errors / ommissions from data entry.

I would prefer to be able to use the offset function if possible. See attached.
2-7-12.xlsx

nutsch

You're missing the width info on your offset

=OFFSET(Patient Info!$A$13,0,0,COUNTA(Patient Info!$A:$A)-1,width)

Thomas

nutsch

Try

=OFFSET(Patient Info!$A$13,0,0,COUNTA(Patient Info!$A:$A)-COUNTA(Patient Info!$A$1:$A$13)+1,12)

wrt1mea

ASKER

Thanks for the reply Nutsch...

OK, when I try to insert the last formula you provided into the range for the dates, I get "That function is not valid."

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

try the following dynamic ranges:

chtDate ='Patient Info'!$A$13:INDEX('Patient Info'!$A:$A,MATCH(99^99,'Patient Info'!$A:$A,1))
chtWeight =OFFSET(chtDate,0,1)

See attached. I've applied them to the chart.

cheers, teylyn
2-7-12.xlsx

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Note:

- the formula you posted and that nutsch then amended is missing the single quotes around the sheet name. If you put them in, then that formula used in a range name will be valid

=OFFSET('Patient Info'!$A$13,0,0,COUNTA('Patient Info'!$A:$A)-COUNTA('Patient Info'!$A$1:$A$13)+1,12)

On the other hand, a range like that cannot be used in a chart, so you need to adjust the width settings to suit the chart series.

For the Dates
chtDate =OFFSET('Patient Info'!$A$13,0,0,COUNTA('Patient Info'!$A:$A)-COUNTA('Patient Info'!$A$1:$A$13)+1,1)

For the numbers
chtNumbers =OFFSET('Patient Info'!$A$13,0,1,COUNTA('Patient Info'!$A:$A)-COUNTA('Patient Info'!$A$1:$A$13)+1,1)

or as above

chtNumbers =Offset(chtDate,0,1)

cheers, teylyn

wrt1mea

ASKER

Can you provide a step by step how to? I know you have technically answered the question....but I need to be able to know how to do it myself.

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wrt1mea

ASKER

PERFECT! THANK YOU SOOOOOOOOOOO MUCH FOR GOING THE EXTRA MILE!

I CANNOT THANK YOU ENOUGH!