Offset Function For Another Sheet: Reference point error

Hi There,

I'm having a challenge with offset formula. For some reason, the reference point is invalid. Can anyone help me out here? Offset reference.png

The full formula that I'm trying to type is: =OFFSET(Allocation!$F$2, 1, match(Monthly!$B6, Allocation!$F$2:$N$2, 0)

Thanks in advance

Try using semi-colons instead of commas.

Thanks a lot! worked like a charm with semicolons!

Glad to help & thanks for the feedback.

Besides the formula help that you received I would like to suggest to you the transformation of your "array" in Allocation!A2:N12 in to a named table so that you wouldn't be managing so many range names, instead you would use the table names like I did in Monthly!B10 in the Worksheet attached.

I'm still struggling with OFFSET function. The following formula =OFFSET(Allocation!$F$2;1;MATCH(Monthly!$B6;Allocation!$F$2:$N$2;0)-1)returns me to sell F3, G3,H3 etc,
How I can modify the formula that it picks the cells corresponding to project name insted of returning me to the same row #3?

I inserted WideBoyDixon formula in the worksheet attached and also put the formula below for you to decide.

Formula:

Please Login or Register  to view this content.