|
Template
I need a formula that will autmatically select a cell based on the cell next
to it being populated or not. For example A B C XX 123 XX 124 125 I need a formula on my template which will pick a number (Our invoice #) in cloumn B if the same row in column A is blank and will then choose the next number in column B everytime column A is populated? Any ideas? Cheers -- Nenagh |
Template
Can you give us more than one example of what you are looking for and show
us what should be "picked" for each of those examples? Also, exactly what do you mean by "pick a number"... pick it where, show it how/where? Rick "Nenagh" wrote in message ... I need a formula that will autmatically select a cell based on the cell next to it being populated or not. For example A B C XX 123 XX 124 125 I need a formula on my template which will pick a number (Our invoice #) in cloumn B if the same row in column A is blank and will then choose the next number in column B everytime column A is populated? Any ideas? Cheers -- Nenagh |
Template
Hi Rick
Basically i have a column of sequential numbers, which are the invoice numbers. When i open my invoice template i use one of these numbers and indicate in the column next to it our client code. What i would like to happen is for the template to recognize a used number, i.e the invoice number in column B has a client code net to it in say column A and chose the next available number, i.e a number in column B that has no client code next to it in column A. Hope this makes sense? Cheers Nenagh When -- Nenagh "Rick Rothstein (MVP - VB)" wrote: Can you give us more than one example of what you are looking for and show us what should be "picked" for each of those examples? Also, exactly what do you mean by "pick a number"... pick it where, show it how/where? Rick "Nenagh" wrote in message ... I need a formula that will autmatically select a cell based on the cell next to it being populated or not. For example A B C XX 123 XX 124 125 I need a formula on my template which will pick a number (Our invoice #) in cloumn B if the same row in column A is blank and will then choose the next number in column B everytime column A is populated? Any ideas? Cheers -- Nenagh |
Template
Okay, I understand which value you want (the client code in Column B that is
next to the first blank entry in Column A), but I'm still not completely clear about your use of the word "choose". Assuming you mean you want a formula that you can place in a cell which will show the client code you are looking for, then try this array-entered** formula... =INDEX(B1:B200,MIN(ROW(A1:A200)*IF(A1:A200="",1,99 ))) ** Commit this formula with Ctrl+Shift+Enter, not just Enter by itself. Change the three occurrences of the number 200 to the largest row number you ever expect to have data in (I just took a guess at 200 rows of data maximum). Rick "Nenagh" wrote in message ... Hi Rick Basically i have a column of sequential numbers, which are the invoice numbers. When i open my invoice template i use one of these numbers and indicate in the column next to it our client code. What i would like to happen is for the template to recognize a used number, i.e the invoice number in column B has a client code net to it in say column A and chose the next available number, i.e a number in column B that has no client code next to it in column A. Hope this makes sense? Cheers Nenagh When -- Nenagh "Rick Rothstein (MVP - VB)" wrote: Can you give us more than one example of what you are looking for and show us what should be "picked" for each of those examples? Also, exactly what do you mean by "pick a number"... pick it where, show it how/where? Rick "Nenagh" wrote in message ... I need a formula that will autmatically select a cell based on the cell next to it being populated or not. For example A B C XX 123 XX 124 125 I need a formula on my template which will pick a number (Our invoice #) in cloumn B if the same row in column A is blank and will then choose the next number in column B everytime column A is populated? Any ideas? Cheers -- Nenagh |
Template
Another way - non-array:
=INDEX(B1:B100,MATCH(LOOKUP(2,1/(A1:A100<""),A1:A100),A1:A100)+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... Okay, I understand which value you want (the client code in Column B that is next to the first blank entry in Column A), but I'm still not completely clear about your use of the word "choose". Assuming you mean you want a formula that you can place in a cell which will show the client code you are looking for, then try this array-entered** formula... =INDEX(B1:B200,MIN(ROW(A1:A200)*IF(A1:A200="",1,99 ))) ** Commit this formula with Ctrl+Shift+Enter, not just Enter by itself. Change the three occurrences of the number 200 to the largest row number you ever expect to have data in (I just took a guess at 200 rows of data maximum). Rick "Nenagh" wrote in message ... Hi Rick Basically i have a column of sequential numbers, which are the invoice numbers. When i open my invoice template i use one of these numbers and indicate in the column next to it our client code. What i would like to happen is for the template to recognize a used number, i.e the invoice number in column B has a client code net to it in say column A and chose the next available number, i.e a number in column B that has no client code next to it in column A. Hope this makes sense? Cheers Nenagh When -- Nenagh "Rick Rothstein (MVP - VB)" wrote: Can you give us more than one example of what you are looking for and show us what should be "picked" for each of those examples? Also, exactly what do you mean by "pick a number"... pick it where, show it how/where? Rick "Nenagh" wrote in message ... I need a formula that will autmatically select a cell based on the cell next to it being populated or not. For example A B C XX 123 XX 124 125 I need a formula on my template which will pick a number (Our invoice #) in cloumn B if the same row in column A is blank and will then choose the next number in column B everytime column A is populated? Any ideas? Cheers -- Nenagh |
| All times are GMT +1. The time now is 09:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com