Trouble with excel changing column in formula!!

[Fadge2236]

Hello,

I am new here! It seems like a useful place to come.

Ok so I have encountered a problem, I have created a table in excel and input data using the form button. I have a formula in one of my cells and is as follows:

=IF(ISBLANK(S2),"",IF(NETWORKDAYS.INTL(MAX(D2:F2),S2-1,1)<1,1,(NETWORKDAYS.INTL(MAX(D2:F2),S2-1,1))))

Now I have got the exact same formula in a separate sheet and it works fine, the 2 sheets are identical, in fact I just copied the first sheet. However when I enter a data into the second sheet excel does this:

=IF(ISBLANK(S2),"",IF(NETWORKDAYS.INTL(MAX(D3:F4),S2-1,1)<1,1,(NETWORKDAYS.INTL(MAX(D3:F4),S2-1,1))))

Where it should be MAX(D3:F3) in both instances. It also changes the original one to MAX(D2:F3) but it was fine before I entered more data in.

Is there something horrifically wrong with my formula? Why does it work on the original sheet?

Any help would be absolute fantastic. Btw I am running excel 2010.

Thanks

[GC Excel]

Hi,
Difficult to answer without seeing your workbook but...
1. Are the formulas entered in the same row?
2. When you say you entered more data in, did you add rows above the formula?
3. Is is really MAX(D2:F2) or rather MAX($D$2:F2) ?

If possible, please attach a sample workbook...

[Fadge2236]

Hello,

Thanks for the reply!

So basically what I have done is create a table and then I used the form button to add new entries to that table. So i put the formula in the first row which was created in the table, when I add data using the form it is supposed to copy the formula down however it is changing that one value. The form function adds new rows for me.

Yeah I copied the formula out of the box and pasted it in.

How do people share workbooks on here?

Thanks

[shg]

The forum rules tell you everything you need to know.

[Fadge2236]

Book1.xlsx

Please excuse the headings I have had to change them and make a new workbook. In making a new sheet it seems the formula doesnt work on that either. The formula is in cell X2.

Thank you for your help

[Aardigspook]

I don't know why the formula apparently changes from the one you say you copied/pasted, but if you change the formula in X2 to use D2:F2 then all the subsequent rows will change as well (to D3:F3, D4:F4, etc) - i.e. you only need to change the first row.