Excel keeps randomly changing my formulas for no apparent reason!

Hi all,

I'm getting a little frustrated (!!)

I have some formulas, quite simple ones, that excel randomly keeps changing for no apparent reason. i.e.

=(COUNTIF(Data!E2:E279, "t1"))+(COUNTIF(Data!I2:I279, "t2"))+(COUNTIF(Data!M2:M279, "t3"))+(COUNTIF(Data!Q2:Q279, "t4"))+(COUNTIF(Data!U2:U279, "t5"))

Where the cell reference is E279 - this didn't use to say this. It said E679, I679 etc. AFter I have run the macros that perform the necessary formulae, excel magically changes the cell reference. I have no idea why or how. Does anyone have any ideas how to fix this or why it is doing it? Or at least how to prevent it.

I am meant to be using this spreadsheet at work tomorrow, and with this issue, I can't!! Is it possible instead of e2:e679 to say e2:the last row of data in column e?

Many thanks - please let me know if you need more information. And I promise Ihaven't changed it and forgotten or that any other macro intereferes with them!!!

Without seeing your macro or you workbook it is difficult to say exactly what is happening.

Why don't you use full-column references?

Hope this helps.

Pete

what do you mean by "full column references"?

I can't send the whole database because of the type of data it contains. I can change the column references to be e1048576 I guess, then at least each time excel reduces it it buys me time! I can't let it go below e679 as this is the range I need to capture.
I'm just attempting to run it on my husbands machine incase it's an excel updae type thing - I'm still running xp and office 2007....

The same problem sitll occurs on my hubbys laptop. Each time I run the macro it is changing all the formula in my spreadsheet. I have literally no idea why and why it has suddenly satarted doing this. The data I've analysed forms art of a presentation tomorrow - any ideas wwould be gratefully gratefully received!!!

What if you lock the reference? I'm guessing your macro is moving lines and the refernced cells are adjusting to.

i.e., Data!$E$2:$E$279 instead of what you are using Data!E2:E279

I've tried that but to no avail, but I do think I may be starting to get somewhere...
I think it's connected to the deletion of rows, that excel is changing the reference in accordance to deleted rows.

If I use the INDIRECT function (which I only found out exists approx thirty seconds agoo.....) would this work and do you know the correct syntax?
?
=(COUNTIF(Indirect(Data!"E2:E279", "t1"))+(COUNTIF(Data!I2:I279, "t2"))+(COUNTIF(Data!M2:M279, "t3"))+(COUNTIF(Data!Q2:Q279, "t4"))+(COUNTIF(Data!U2:U279, "t5"))

Thank you so much!

Full-column references means using E:E instead of E2:E279, i.e. with no row details, so your formula would become:

=COUNTIF(Data!E:E,"t1")+COUNTIF(Data!I:I,"t2")+COUNTIF(Data!M:M,"t3")+COUNTIF(Data!Q:Q,"t4")+COUNTIF(Data!U:U,"t5")

I've also removed unnecessary brackets and spaces.

If you want to use the INDIRECT function, then the syntax would be:

=COUNTIF(Indirect("Data!E2:E279"),"t1")+COUNTIF(Indirect("Data!I2:I279"),"t2")+COUNTIF(Indirect("Data!M2:M279"),"t3")+COUNTIF(Indirect("Data!Q2:Q279"),"t4")+COUNTIF(Indirect("Data!U2:U279"),"t5")

Hope this helps.

Pete

thanks Pete.

Do you think there is a preferential way - I've just changed all my formulas to include the "indirect" and it seems to work now (thank goodness). Do you think that the e:e, i:i way would work betteR?

Thanks again for your help!

The INDIRECT function is volatile, which means the formula will be calculated whenever there is a change on the workbook - this could slow the workbook down. Using full-column references is fine, as the COUNTIF function is clever enough to only use the used range. And, the formula is simpler.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete