Lookup (or IF) the next unique number based on a value of Y or N in another cell

**tommyz03** · 12-18-2012, 11:47 AM

I have a receiving log spreadsheet where if an item needs a certain type of inspection (Y or N) a log number will be assigned in another cell. The column with the log numbers needs to take the next consecutive number in a series. Here is what I have:
A B AA
1 Y 101 101
2 N N/A 102
3 Y 102 103
4 104
5 105

The formula I have in B1 is =IF(A1=”N”,”N/A”,AA1)
Two questions:
1)When referencing column AA in the IF statement, the result in column B will return only the cell value related to the given row. Is there a way to have excel automatically sequence based on this need?
2) If I copy the formula in B1 down column B so it automatically assigns a number once there is data in column A, when the cell is blank it still returns the value associated to the row #, I need it to be blank.

Thanks in advance!

**Melvinrobb** · 12-18-2012, 01:13 PM

I'm not sure if I know what you want to have show, but would the following formula in B1 work?

Formula:

Please Login or Register  to view this content.

**tommyz03** · 12-18-2012, 01:48 PM

I don't know if my other reply went through, but the formula didn't work.

If column A = N, N/A returned just fine.
If column A = "", "" returns just fine.
If column A = Y, #VALUE! is what returns...

Thanks,

**Melvinrobb** · 12-18-2012, 01:53 PM

Here is what I have. If it is giving you an error message, it is because I am not interpreting where your data is correctly.

**FDibbins** · 12-18-2012, 02:04 PM

if you want the number to increase by 1 each time, and you dont actually have those numbers anywhere, you can use the =max(range-above)+1 to reference the range above and add 1

**tommyz03** · 12-18-2012, 02:13 PM

For some reason my reply's aren't seeming to post properly, so here is another try.

Melvin, you are correct - your solution works great. I had other text prefacing my number series as kind of a logical indicator for auditors. If I want to keep the preface text would a concatenate formula be the best solution? or is there a way to imbed text into your solution?

**Melvinrobb** · 12-18-2012, 03:05 PM

Not sure what you mean. I believe the concatenate function would be what you want, but can you give an example of what you mean?

**FDibbins** · 12-18-2012, 03:12 PM

the only way you will be able to use concatenation if the cell above has text and a number in it eg "this is row 12", would be to extract the number 1st, then convert it to a value, add 1 to it, and then re-insert the text. so, if "this is row 12" was in A1, then A2 would use this formula...
="this is row "&MID(A1,13,2)*1+1
copied down

**Melvinrobb** · 12-18-2012, 03:14 PM

Thanks for stepping in FDibbins.
I realized what he meant as soon as i read your thread.

**tommyz03** · 12-18-2012, 03:40 PM

Thanks guys, I'll play with this a little and get back to you. I'm in the middle of inventory counts...

thanks again, you guys are great!! greatly appreciated,,

**tommyz03** · 12-18-2012, 07:44 PM

Hi FDibbins,

I somewhat understand what your formula is doing, but I don't understand how to apply it with Melvinrobb's solution. You indicate to copy down the formula you provided, but where and what other fields would reference the column where this is copied down? From my perspective, your code/formula would have to be part of Melvinrobb's IF statement to get what I'm hoping to retrieve.

I hope I'm making sense...

**FDibbins** · 12-19-2012, 12:10 AM

i dont see a workbook from you to relate to, so assume your numbering is in column A, you would enter the 1st "seed" in A2 (assuming A1 has a heading), then copy that formula down. to make sure it only appears when something is entered in that row, you can tie it to column B (or any column that will have info in it), by wrapping it in an if statement...

=if(B2="","","="this is row "&MID(A1,13,2)*1+1) obviously you will need to change the "this is row " part, and also the ,13, i will explain that below

what ="this is row "&MID(A1,13,2)*1+1 does is as follows...
1st it strips out the text by getting the MIDdle string from A1 (the cell above), starting at character 13 (this is row =12 characters, +1 for the space), and returning the next 2 characters (change that to 3 if you will have more than 99 numbers)
then it takes the above - which is text, although it looks like a number - and multiplies it by 1 to convert the text number to a real number, and adds 1 to it
finally it concatenates (joins) the text with the increased number

hope that makes sense, if not shout

**tommyz03** · 12-19-2012, 01:23 AM

I'd be happy to attach a workbook with my data, but I don't think my access allows me to.

I'll check in the morning when i get back to work...

**tommyz03** · 12-19-2012, 02:38 PM

FDibbens, here is the file if you can look at it, there are two worksheets, the one named sample is where i've been experimenting.

thx!

Lookup (or IF) the next unique number based on a value of Y or N in another cell

LinkBack

Thread Tools

Rate This Thread

Display

Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Re: Lookup (or IF) the next unique number based on a value of Y or N in another cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions