Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

offset question

Posted on 2011-04-28
6
Medium Priority
?
238 Views
Last Modified: 2012-05-11
Hi,

I am looking at a spreadsheet and trying to figure exactly hows its working.

There is a formula

=OFFSET(Data!$B300:$B300,0,COUNT('Summary Sheet'!C$30:$O$30))

Can someone explain how an offset works and what the above is saying.

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 3
  • 3
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35482345
The first argument is the start position: Data!$B300:$B300 (i.e. B300 on the Data sheet)
then the formula offsets by 0 rows (in other words still looking at row 300)
and then offsets the column by the the number of entries in C30:O30 on the Summary sheet
so if you had 5 cells filled in C30:O30, you would be referring to the cell 5 columns to the right of B300.

Note the formula would normally be written as just:
=OFFSET(Data!$B300,0,COUNT('Summary Sheet'!C$30:$O$30))

Make sense?
0
 

Author Comment

by:Seamus2626
ID: 35482359
What if you had 4 cells filled in C30:O30, would that mean you would be referring to the cell 4 columns to the left of B300?


Thanks
Seamus
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35482368
To the right, not left, but yes. :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:Seamus2626
ID: 35482377
I understand! perfect, thanks Rory

Seamus
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35482387
Even though they are not used here, there are also 2 optional arguments to the OFFSET function whereby you can specify the number of rows and columns in the resulting range. So:
=OFFSET(A1,0,0,3,2)
would refer to a range starting 0 rows and 0 columns from A1 (i.e. starting at A1) that is 3 rows high and 2 columns wide - in other words A1:B3
0
 

Author Comment

by:Seamus2626
ID: 35482905
Thanks for the extra info!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question