Elizabeth has attended:
Excel Advanced course
Excel Intermediate course
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
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
Dynamic Ranges.xls
