• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

offset function with cell references

Tigger

New Member
I have recently inherited a new position at work along with a new spreadsheet to record the companies chemical manifest. If you answer yes in column j then all the information is posted on a second worksheet using an offset function. I understand the offset parameters but the spread sheet is using weird cell references that i dont understand . =MATCH($A$1,OFFSET(Manifest!$J$8:$J$162,A2,0),0)+A2-1
the above matches "yes" in a1 then gives me a cell reference but i dont understand this part =OFFSET(Manifest!$A$8,$B10,D$9). The manifest is sheet one. Thanks in advance
 

Attachments

  • Chemical Manifest (2).xlsx
    319.9 KB · Views: 7
Hi ,

Columns A , B and C on the tab named Manifest are hidden ; if you unhide these columns , you can see what values are present in column B. The same goes for row 9.

Narayan
 
I know that the column are hidden i have unhiden them that how i got the match code. I just dont understand the offset formula. the parameters for offset are =offset(reference cell, cells up or down, cells left or right,[],[]) eg =offset(a1,2,2). I dont understand the =OFFSET(Manifest!$A$8,$B10,D$9) the b10,d9 cell references shouldnt they be values not cell reference?
 
@Tigger

Unhide row no 9..and column A: C, and you will see values stored...once you evaluate the formula it becomes...Offset(Manifest!$A$8,4,1)...where B10 referes to 4 and D9 refers to 1..

Note: it can either be a cell reference or a value...both are fine...depends on the requirement..

Hope this helps
 
Thanks heaps that has helped me understand. I was getting mixed up and thought the B10 and D9 reference was from the manifest page. All good and thanks again.
 
Back
Top