Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Excel 2003, I am trying to keep track of some petty cash, and have it set up with the denomination along the top row, along with a sub total and difference column. I want a small section that shows how many rolls of coins I should have, by taking the total amount, and dividing it by however many should be in a roll, and rounding to the lowest whole number. That part is fine.

What I want done is for that ONE section (how many rolls) I should have, based on the last row that has information in it. For example, if the last row is row 13, it should read the data from B13, C13, D13, etc.

I don't mind learning Macros, if that's what the solution requires. I don't want to be manually selecting the last row each time though, I just want the worksheet to know automatically.

EDIT: What I mean is that I want to have the last cells be selected for a formula, INT(B2/25), replacing B2 with the last row, and the number from 2-6 (depending on the cell).

share|improve this question
    
Sorry about the confusion –  ekaj Dec 1 '12 at 7:04
1  
I realized after I read the link and re-read my question. No harm done, thanks :) –  Canadian Luke Dec 1 '12 at 7:05

1 Answer 1

up vote 1 down vote accepted

Assuming you have no skipped rows in column B, this will return the value of the last used cell in Column B. =INDIRECT("B" &ROWS(A:A)-COUNTBLANK(INDIRECT("B" & ROW()+1 &":B" & ROWS(A:A))))

So your completed formula would be something like:

=INT(INDIRECT("B" &ROWS(A:A)-COUNTBLANK(INDIRECT("B" & ROW()+1 &":B" & ROWS(A:A))))/25)

This is using ROWS(A:A) instead of hardcoding the number of rows so that it will still work when you upgrade from Excel 2003.

It uses Row()+1 so that you can freely place this in Column B itself without having the issue of circular references.

Because it uses Row()+1 will not work correctly if placed in a row below the row of the last value in column B.

share|improve this answer
    
This just returns a 0. I tried reading the help file about INDIRECT, but it isn't making sense to me. Can you explain it a bit better? –  Canadian Luke Dec 2 '12 at 7:01
    
Indirect allows you to reference a cell by providing a the text of the reference. So something like Indirect("A" & B1) would end up being Column A and then whatever Row # corresponds to the value of B1. The only reason what I provided is resulting in 0 is that you must have at least 1 skipped row in Column B, as in a blank cell. If you want to keep the blank cells add a minus # of blank cells just before the last closing parenthesis, like so: =INDIRECT("B" &ROWS(A:A)-COUNTBLANK(INDIRECT("B" & ROW()+1 &":B" & ROWS(A:A)))-1) –  Daniel Cook Dec 2 '12 at 15:02
    
I got it working with that last command you gave me, but I took out the -1 at the end. Thank you very much for your help –  Canadian Luke Dec 2 '12 at 18:38
    
I figured out why it kept giving me a 0. It only works if the last row is the row the formula is on, or lower –  Canadian Luke Dec 2 '12 at 20:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.