OFFSET formula not working when dragged

**BadBoy House** · 11-02-2022, 08:19 AM

Hello all,

I have an Excel sheet which contains a basic formula to divide a month total by 7 (to get a daily amount) and then it multiplies this by the number of days in the month.

On the spreadsheet there is a blank column between each month and so dragging the formula does not work.

I've created an OFFSET formula which takes into account the column in between each figure, but when I drag the formula it doesn't increase the number of columns from the reference cell. It should increase the number of columns by 2 each time.

I hope this makes sense - I've attached a sample sheet which contains the data and formulas.

Can anyone point me in the right direction?

Thanks in advance

**romperstomper** · 11-02-2022, 08:23 AM

You could use:

=INDEX($C$3:$I$3,MATCH(DATEVALUE("1-"&C7&"-2023"),$C$2:$I$2,0))/7*C6

in C8 and copy across.

**BadBoy House** · 11-02-2022, 10:31 AM

Thanks Rory,

That looks like it will work. I'll need to adapt it for the 2024 dates but on the 2023 ones it works great.

Many thanks

**romperstomper** · 11-02-2022, 11:22 AM

It would be simpler/better if you could alter the headers in row 7 to be the actual dates in row 2, then just format them to show the month name, if preferred.

**FlameRetired** · 11-02-2022, 04:43 PM

Administrative Note:

Members will tailor the solutions they offer to the version that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

OFFSET formula not working when dragged

LinkBack

Thread Tools

Rate This Thread

Display

OFFSET formula not working when dragged

Re: OFFSET formula not working when dragged

Re: OFFSET formula not working when dragged

Re: OFFSET formula not working when dragged

Re: OFFSET formula not working when dragged

Thread Information

Users Browsing this Thread

Similar Threads

OFFSET / ISBLANK Not Working in Array Formula??

[SOLVED] Offset formula not working together with if

[SOLVED] Choose formula (instead of Nested IFs) not working - wish for it to also be dragged down.

Made some changes, formula stopped working (using MATCH and OFFSET)

Offset Formula - Not working

[SOLVED] Offset function across columns when dragged down rather than rows.

[SOLVED] Offset, Index, Match formula with dates inconsistently working

Bookmarks

Bookmarks

Posting Permissions