The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Offset function and formulas

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Offset function and formulas

Resolved · Low Priority · Version 2007

ReplyTue 20 Apr 2010, 16:49Delegate Elizabeth said...

Offset function and formulas

Can I be cheeky and ask 2 questions?

Firstly I hear you have a useful example on how the offset function works, could you send it to me?

Secondly is there a way to display the formula you are using in a cell? I know that you can see it when selected but if you move around a sheet how can you still see it?

Thanks

Liz

For upcoming training course dates see: Pricing & availability

ReplyThu 22 Apr 2010, 10:36Trainer Stephen said...

RE: Offset function and formulas

Hi Elizabeth

Thanks for your question

First I have created a workbook that uses the offset function to create a dynamic named range. In this example we have a column of names, and a dynamic named range that will grow or shrink as we add or delete names from the list this is then used to populate a drop down list that will always include items in the list.
If you click on the name manager you will see the formula which is
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
Regarding displaying formulas in cells, simply click on the office button, select excel options and go to advanced. scroll down until you get to the display options for worksheet and select "Show formulas in cells instead of calculated results".

Hope this is useful

Stephen

Attached files...

Dynamic Ranges.xls

Wed 28 Apr 2010: Automatically marked as resolved.

Excel articles

· You're Hired! Raising Your Chances Of Employment With Excel
· Avoid Duplication In Excel By Ensuring Unique Values
· Customized Financial Excel Courses in London
· Improve Performance Of Your Excel Macros
· Microsoft Excel can ensure you keep on top of your finances

Please browse our web site to find out more about
excel training seminar and other Microsoft training courses.

Excel tip:

COUNT function vs COUNTA function

The COUNTA function works in the same way as the COUNT function, except that it will count cells that contain text (labels) and also cells that contain numbers (values). The COUNT function will only count cells that contain numbers. Blank cells are not counted by either the COUNT or the COUNTA function.

View all Excel hints and tips

Is it better to switch more between excel and project

» Forum post: Intergrate excel with project

Rate this page:

Currently 2.15/5
1
2
3
4
5

2.2/5 (213 votes cast)

Add to:

Follow @beststl on Twitter

© 2012 Best STL. All Rights Reserved. A Microsoft software training company. info
All prices offered for business users and exclude VAT. E&OE · Terms and Conditions