OFFSET & Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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


Cindy said:
I have a worksheet with a total page of item inventory and then seperate
Click to expand...
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
Click to expand...
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
Click to expand...
responses to this. As you can tell, I am NEW to the advanced worksheet
function area.
 
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
 
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

Cindy said:
That works great except that I have other formula's in columns to the
Click to expand...
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
Click to expand...
to make it easier? I just wanted to be able to name the range in C and use
the last updated value (not sum).
 
Thank you so much for the assistance-it is greatly appreciated!

Have a wonderful weekend.

Regards,
Cindy
 
Back
Top