#REF! when sorting

[twelec]

We have an issue with sorting rows of data that have formulas in them.

---------------
These formulas are using data from a row which is above the column headers.

For example cell H2 has 160 manually entered in it. It's the number of work hours available in January 2009.

The column header for that column is in Q4 and has a value of 'Jan'

Below this in cell H5 is the formula '=+H2' because the resource in this row is available for 100% of the month.

Another resource in row 22 has the formula '=+H2*0.15' in cell H22 because they are only available 15% of the time that month.

---------------
The problem occurs when we try to sort this data. Most of the cells displays #REF!. Some show a value, but the calculation is wrong.

Is there a solution to this issue?

Thank you very much,

Chuck

I've attached the unsorted spreadsheet.

[martindwilson]

convert to absolute referencing i.d =$H$2
incidently
=+$H$5+$J$5+$L$5+$N$5+$P$5+$R$5+$T$5+$V$5+$X$5+$Z$5+$AB$5+$AD$5
could be replaced by
SUMPRODUCT((1-MOD(COLUMN(H5:AD5),2))*H5:AD5)

[twelec]

thank you very much - for both suggestions. this is my first time on the site, but not my last. didn't expect such a quick response.

i was in an excel class yesterday & posed the same question - i was told it's impossible to do. very happy to find out different.

thx again, chuck

[arthurbr]

And also be aware that in formulae beginning with "=+" the "+" is not necessary, just an heritage from the Lotus period

[ChemistB]

i was in an excel class yesterday & posed the same question- i was told it's impossible to do.

Martin specializes in the impossible.

[twelec]

convert to absolute referencing i.d =$H$2
incidently
=+$H$5+$J$5+$L$5+$N$5+$P$5+$R$5+$T$5+$V$5+$X$5+$Z$5+$AB$5+$AD$5
could be replaced by
SUMPRODUCT((1-MOD(COLUMN(H5:AD5),2))*H5:AD5)

Re: #REF! when sorting

--------------------------------------------------------------------------------
should have replied to directly your response - just want to thank you again - worked like a charm, chuck

[arthurbr]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[smith_ts]

Is there a key press to when selecting a cell to make it an 'absolute' reference?

[shg]

Please read the forum rules about posting questions in other people's threads, and then start your own.

Thanks.