Puzzled, why do these change when supposedly Absolute?

E

Eldraad

I have a workbook that contains an INPUT sheet, 5 sheets containing dat
imports from text report files and one sheet that is all formulas.

The INPUT page was modified to pull information from the formulas pag
so I never had to spend the 90 minutes typing in all of the numbers.
This is the part people look at (there are 8 other sheets that pul
THEIR numbers from this page as well...very hard to modify the 8 page
so I modified the INPUT page only).

The formulas page contains all of the formulas used to get th
information needed from the 5 worksheets containing the imported data


An example from the formulas page:

=OFFSET('Statistical Convert'!$A$1, MATCH("TOTAL 3*",'Statistica
Convert'!$A$2:$A$2000,0),13)

The Problem! When I run a macro on one of the 5 data import worksheet
(the one the above formula checks) the values used in the formulas pag
change! The macro removes blank rows and a few header columns so th
search function in the formula can return the correct numbers withou
blank rows and header info getting in the way.

Suddenly, the $A$2:$A$2000 changes to $A$2:$A$866 and that is below th
amount of rows that the report uses so it returns zeros instead o
data.

What is happening here and how do I prevent it?

Many thanks for any help
 
P

Peo Sjoblom

Absolute references will not stay absolute if you delete rows/columns, only
way to always "lock" a formula
is to use INDIRECT

=SUM($B$1:$B$4)

if you insert a row in row one the above will change to

=SUM($B$2:$B$5)

however this will always stay the same

=SUM(INDIRECT("$B$1:$B$4"))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
K

Ken Wright

Being absolute only fixes the reference for purposes of copying and pasting,
ensuring you still point to where you did originally. If you delete rows via
your clean up macro then the ranges involved will automatically adjust to
reflect the reduced range. Try using something like INDIRECT to refer to a
constant range that will not change regardless, eg:-

=SUM(INDIRECT("A1:A10"))

Even if I delete rows 2 to 8, the range referred to will still be A1:A10

Had the formula been

=SUM($A$1:$A$10)

then deleting rows 2 to 8 would mean your formula would adjust to

=SUM($A$1:$A$3)

INDIRECT converts a textual reference to a range to a real range.
 
E

Eldraad

Okay, thank you for the "INDirect" help...

My problem is now how do I modify the formula I showed above in th
first example to properly include the INDIRECT feature.

I am sure that I keep putting it in the wrong places as I always get
formula error.

Can someone toss me another bone? Lol.

Thank
 
K

Ken Wright

Try this:-

=OFFSET('Statistical Convert'!$A$1, MATCH("TOTAL 3*",INDIRECT("'Statistical
Convert'!$A$2:$A$2000"),0),13)
 
E

Eldraad

Thank you very much Ken Wright!

Following my usual methods I was putting the indirect before the cel
references A1:A2000 or in front of the ToTal 3* and getting an error.
Ah well, I will learn!

Again, Thank You! I can correct this flaw and have the reports up an
running tomorrow.

Eldraa
 
Top