Excel 2019 Format

Simon Leung
Simon Leung used Ask the Experts™
on
Try to set a OFFSET function but it returns an error. Any idea ?

Thx
Excel VBA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Your formula is working for me in Excel 2016/Office 365 Insiders Fast channel. Because that version has dynamic arrays, the formula may be placed in a single cell and will return a column of nine values, five from A1:A4 and four more from A5:A9.

Where were you putting the formula, and what did you hope it would do?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Ah! I bet you have R1C1 addressing turned on. Go to the File...Options...Formulas menu item and clear the checkbox for R1C1 reference style.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I put the formula in cell E1.

With R1C1 reference style, your formula might be:
=OFFSET(OPTIONS!R1C1,0,0,COUNTA(OPTIONS!C1:C[-3]),1)

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you want to return the values in A1:A5 (and have changed back to A1 reference style), you might use:
=OFFSET(OPTIONS!$A$1,0,0,COUNTA(OPTIONS!$A:$A),1)

Open in new window

Author

Commented:
Thx. It works now.
However, even though I enter some invalid valid, it doesn't prompt for any error. Any idea ? I use List Data validation for validation purpose.
Error2.png
Finance Analyst
Commented:
Check the Data Validation settings on the Error Alert tab:
Data ValidationThe tick box at the top needs to be ticked, it is normally ticked by default so maybe has been unticked for some reason.

Author

Commented:
Thx, but this one has been clicked in my Excel but it doesn't help.

Author

Commented:
Thx. find my problem, it is the ignore blank checkbox issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial