How to show the next number depending on other data

[rob nobel]

Any assistance would be greatly appreciated.
Column F is used to enter account names (7 in all & could grow), and Column
G is used to enter cheque Nos.
Cell F25 onwards is where the first account names are entered and ,
G25 onwards is where the corresponding cheque numbers of the coorespnding
account is entered.
My question is.....
Is there a way to have other cells down column G (say G30, etc.), show what
the next cheque number would be if the same account name is entered in F30
that was shown in F25 (bearing in mind that other accounts and cheque
numbers will be entered in the intervening rows?
(In other words, each time I enter an account name in column F, the next
available cheque number will appear in the corresponding row in column G.)
(I have got the Data Val in column F check for blank cells so that no rows
can be missed when entering data - if that helps. vis,
=IF($F24<>"",AccountCodes).)
If it is possible, I would prefer that only the next number can be entered,
to make sure no numbers are missed. There will be occasions when a cheque
number is not required, however (such as bank fees, auto deductions, etc. so
there will be a need for blank cells in column G).
While I'm writing this I can see a problem that someone might skip a cheque
or even begin using a cheque book before the first is finished , (although
that can be overcome by just entering them in anyway without the rest of the
details.)
Thankyou in advance.
I'm using office 2000
Rob

 

[Dave Peterson]

Maybe you could just use a formula in column G:

=if(f25="","",max($f$1:F24)+1)
(and drag down)

You'll have to adjust those ranges to match your data.

 

[rob nobel]

Thanks Dave, I think this is starting to take shape using your formula as
follows:
IF(F25="","",MAX(G$25:G25)+1).
Trouble is though, that I need the next number to be related to the Account
name in column F. So if Column F is "Gen", then the max+1 should be the
max+1 for only that account. This would need to occur for all seven
accounts.
Do you think this is possible, as if not, I may have to separate all the
accounts into different worksheets which I'd like to avoid, if possible.
Rob

 

[Dave Peterson]

How about:

=IF(F25="","",MAX(IF($F$1:F24=F25,$G$1:G24)+1))
instead of just hitting enter, hit ctrl-shift-enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

===
Sorry, I didn't understand you're original post.

 

[rob nobel]

Thanks for you quick response Dave. I'm sorry I can't explain it better but
it seems your last suggestion may give me some ideas to work on.
I may have to post back later if I still can't get it to work.
PS. Amazingly, I do understand how to enter an array formula, just can't
understand how they work which means (even after reading online help), so
it's hard to construct one.
Just as a matter of clarification, do the 2 ranges in your formula
$F$1:F24=F25,$G$1:G24 need to be the same length for this to work.
Rob

 

[rob nobel]

Durn!
One major problem. This formula works great, except if I try to save the
file once I've copied the formula down about 10 rows, Excel freezes with
"illegal operation..."
This is what I have so far in cell G26 (using an amended version of your
formula) .....
=IF(F26="","",MAX(IF(F26=$I$5,G$25:G25)+1))
(row 25 is the starting point.)
If the Excel blowout can be fixed then the next problem is....
It works great for the one account, but blowed if I can get it to work if I
enter the other accounts into this procedure.
If I explain some more you maybe able to assist further?
Notes:
1. Column F (row 26 onwards) is where I enter the account name (via data
validation dropdown with list referenced to I5:I11) .
2. Column G (row 26 onwards) is where the next cheque number for that
account should show (or be deleted, if a non chq entry is to be made).
3. Range I5:I11 is the list of account names to choose from.

I need the next chq number to appear (in the same row in column G), for
whatever account is selected (in column F).
Thank you so far,
Rob

Thanks for you quick response Dave. I'm sorry I can't explain it better but
it seems your last suggestion may give me some ideas to work on.
I may have to post back later if I still can't get it to work.
PS. Amazingly, I do understand how to enter an array formula, just can't
understand how they work which means (even after reading online help), so
it's hard to construct one.
Just as a matter of clarification, do the 2 ranges in your formula
$F$1:F24=F25,$G$1:G24 need to be the same length for this to work.
Rob

How about:

=IF(F25="","",MAX(IF($F$1:F24=F25,$G$1:G24)+1))
instead of just hitting enter, hit ctrl-shift-enter. If you do it
correctly, excel will wrap curly brackets {} around your formula.

(don't

type
them yourself.)

===
Sorry, I didn't understand you're original post.

entered

in

the

 

[rob nobel]

Just a quick add-on.
The problem of Excel freezing is overcome if I copy and paste each cell
instead of dragging to copy.
Rob

Durn!
One major problem. This formula works great, except if I try to save the
file once I've copied the formula down about 10 rows, Excel freezes with
"illegal operation..."
This is what I have so far in cell G26 (using an amended version of your
formula) .....
=IF(F26="","",MAX(IF(F26=$I$5,G$25:G25)+1))
(row 25 is the starting point.)
If the Excel blowout can be fixed then the next problem is....
It works great for the one account, but blowed if I can get it to work if I
enter the other accounts into this procedure.
If I explain some more you maybe able to assist further?
Notes:
1. Column F (row 26 onwards) is where I enter the account name (via data
validation dropdown with list referenced to I5:I11) .
2. Column G (row 26 onwards) is where the next cheque number for that
account should show (or be deleted, if a non chq entry is to be made).
3. Range I5:I11 is the list of account names to choose from.

I need the next chq number to appear (in the same row in column G), for
whatever account is selected (in column F).
Thank you so far,
Rob

Thanks for you quick response Dave. I'm sorry I can't explain it better but
it seems your last suggestion may give me some ideas to work on.
I may have to post back later if I still can't get it to work.
PS. Amazingly, I do understand how to enter an array formula, just can't
understand how they work which means (even after reading online help), so
it's hard to construct one.
Just as a matter of clarification, do the 2 ranges in your formula
$F$1:F24=F25,$G$1:G24 need to be the same length for this to work.
Rob

(don't formula
as

and

, etc.),
show entered the that
no

skip

 

[Dave Peterson]

I dragged that formula down (about) 1600 rows yesterday and no freeze. (albeit,
there was data only in F&G.

Does it freeze up when you do it against a new test worksheet. If not, you may
want to try to find the problem (good luck!).

I really haven't had too much trouble with corrupted workbooks/worksheets (only
one that I can recall), but maybe it would be good to see if you could recreate
the worksheet to see if that fixed it. (just a wild guess!)

And if you want more info on those powerful array formulas:

Chip Pearson has notes for array formulas at:
http://www.cpearson.com/excel/array.htm

And you may want to get a copy of Bob Umlas's white paper:
http://www.emailoffice.com/excel/arrays-bobumlas.html

 

[rob nobel]

I tried it on a new workbook, Dave, and no probs. Tried dragging the
formula again on the old workbook and no probs, BUT, if I tried to drag it
to virgin cells it did cause a problem. So I'm none the wiser. I guess
it's due to having a bunch of other formulae on that sheet??
Thanks for the links. I always appreciate any help I can get to teach me
more. Trouble with books is, that they're fine to teach some basics and
even advanced, but the application you work on never seems to fall within
their examples and you need some experience to develop your own complicated
formulae.
Rob

 

[Dave Peterson]

I agree with you. It's quite a learning experience just to lurk in these here
newsgroups. There's always real-life questions. And I like watching the
answers that get generated.

 

[rob nobel]

Dave,
I hope you don't mind but I've copied an earlier post regarding the initual
question of mine as I still need help with it.
This is what I have so far in cell G26 (using an amended version of your
formula) .....
=IF(F26="","",MAX(IF(F26=$I$5,G$25:G25)+1))
(row 25 is the starting point.)
It works great for the one account, but blowed if I can get it to work if I
enter the other accounts into this procedure.
If I explain some more you maybe able to assist further?
Notes:
1. Column F (row 26 onwards) is where I enter the account name (via data
validation dropdown with list referenced to I5:I11) .
2. Column G (row 26 onwards) is where the next cheque number for that
account should show (or be blank, if a non chq entry is to be made).
3. Range I5:I11 is the list of account names to choose from.

I need the next chq number to appear (in the same row in column G), for
whatever account is selected (in column F).
If this can't be done, I guess I'll need to have a sheet for each account.
Thank you so far,
Rob

 

[Dave Peterson]

I used this formula in G25
=IF(F25="","",MAX(IF($F$1:F24=F25,$G$1:G24)+1))
you used (in G26???)
=IF(F26="","",MAX(IF(F26=$I$5,G$25:G25)+1))

I'm not using the validation list at all. I'm checking the cells in column F
above the current row. If it matches, then I'll include the number in G (and
then I'll take all those numbers, determine the maximum and add 1.

This part of the formula:
IF($F$1:F24=F25,$G$1:G24)
will return an array that'll return stuff like:

={1;2;3;4;5;6;7;8;FALSE;FALSE;FALSE;FALSE;FALSE;14;15;16;17;
FALSE;FALSE;FALSE;FALSE;22;23;24}

The false's will be for those rows that F# didn't match F25. The number will be
returned each time F# matched that latest entry in F25.

 

[rob nobel]

Dave,
Thanks for your persistance with me and explaining what it does (and staying
up late). I've finally got it to work, amended as follows....
I used this formula in G26
IF(F26="","",MAX(IF($F$24:F25=F26,$G$24:G25)+1))
(entered as an array)

AND IT WORKS SOOOOOOOO GOOD!!
(I didn't really understand how the array feature worked, so couldn't amend
it before, succesfully.)
Regards,
Rob

 

[Dave Peterson]

Glad you got it working.

Dave,
Thanks for your persistance with me and explaining what it does (and staying
up late). I've finally got it to work, amended as follows....
I used this formula in G26
IF(F26="","",MAX(IF($F$24:F25=F26,$G$24:G25)+1))
(entered as an array)

AND IT WORKS SOOOOOOOO GOOD!!
(I didn't really understand how the array feature worked, so couldn't amend
it before, succesfully.)
Regards,
Rob

<<snipped>>