OFFSET & Count

[Cindy]

I have a worksheet with a total page of item inventory and then seperate worksheets within the workbook for each type of item shown on the total page. The total page carries the last updated entry from the individual spreadsheet linked by this
=OFFSET($C$2,COUNTA($C$2:$C$28)-1,0) to the correct item column. While this works, I can only reference the last cell updated (C28) and not an array for future updates (ie: c2:c175) because if I go beyond C28, I just get a zero and NOT the last truely updated cell which would be C28. How do I correct this so when changes are made, it will automatically go to the last cell updated, as if it were a floating range

I hope I have stated this correctly and I truely appreciate any and all responses to this. As you can tell, I am NEW to the advanced worksheet function area

Regards

Cind

 

[Peo Sjoblom]

If you want to sum a range you can use

=SUM(OFFSET($C$2,,,COUNTA(C:C),))

will sum from C2 to whatever the last cell is

--

Regards,

Peo Sjoblom

I have a worksheet with a total page of item inventory and then seperate

worksheets within the workbook for each type of item shown on the total
page. The total page carries the last updated entry from the individual
spreadsheet linked by this:

=OFFSET($C$2,COUNTA($C$2:$C$28)-1,0) to the correct item column. While

this works, I can only reference the last cell updated (C28) and not an
array for future updates (ie: c2:c175) because if I go beyond C28, I just
get a zero and NOT the last truely updated cell which would be C28. How do
I correct this so when changes are made, it will automatically go to the
last cell updated, as if it were a floating range?

I hope I have stated this correctly and I truely appreciate any and all

responses to this. As you can tell, I am NEW to the advanced worksheet
function area.

 

[Ken Wright]

Assuming no blanks in the range C:C which would throw it out

 

[Cindy]

That works great except that I have other formula's in columns to the right which column C is getting it's total from. So, using this function is now summing the formula results giving me a figure but not the correct one
Here is an example
D2 H3 I
BegTotal UpdtdTotal Date QTY Removed QTY Adde
2 4 01/29/04 0
4 104 01/29/04 10

I have the following formulas in place for Beg Total and Updtd Total respectively

=IF(D2>0,D2,IF(D2<0,D2,0)

=IF(H3>0, D2-H3,IF(I3>0, D2+I3,IF(H3<0, D2,0))

Do you think I should change the way I am getting my totals in the column to make it easier? I just wanted to be able to name the range in C and use the last updated value (not sum)

I appreciate your quick and experienced remarks on this and will use the initial reply in the future

Thanks in advance

Cindy

 

[Peo Sjoblom]

Use absolute references when you name the formula

=OFFSET(Sheet1!$C$2,,,COUNTA(Sheet1!$C:$C))

replace Sheet1 with your sheet name

otherwise it will depend on where you put the formula

--

Regards,

Peo Sjoblom

That works great except that I have other formula's in columns to the

right which column C is getting it's total from. So, using this function is
now summing the formula results giving me a figure but not the correct one.

Here is an example:
D2 H3 I3
BegTotal UpdtdTotal Date QTY Removed QTY Added
2 4 01/29/04 0 2
4 104 01/29/04 100

I have the following formulas in place for Beg Total and Updtd Total respectively:

=IF(D2>0,D2,IF(D2<0,D2,0))

=IF(H3>0, D2-H3,IF(I3>0, D2+I3,IF(H3<0, D2,0)))

Do you think I should change the way I am getting my totals in the column

to make it easier? I just wanted to be able to name the range in C and use
the last updated value (not sum).

 

[Cindy]

Thank you so much for the assistance-it is greatly appreciated!

Have a wonderful weekend.

Regards,
Cindy