Auto Assign Unique Values to Blank Cells

**NSTurk725** · 06-19-2009, 05:00 PM

I am having trouble assigning a unique value to all the blank cells in a column with a Function. If the value is Null, i would like the value of the cell to then be something like "BlankCell1" and so forth. (blankcell2, blankcell3, etc).

I cant figure out what to put in my true statement to automate that. Here's what I have so far:

=IF('ASP DATA FILE'!Q2="","blank",'ASP DATA FILE'!Q2)

Any help would be appreciated.

Thank You

**contaminated** · 06-19-2009, 05:06 PM

If I understood u properly u need get unique values. Take a look at this book. Maybe it will help...

**NSTurk725** · 06-19-2009, 08:17 PM

That function is super long.lol Im having trouble following that. Is there something a little bit simpler?

**contaminated** · 06-20-2009, 03:18 PM

Was it what you had been looking for?

**martindwilson** · 06-20-2009, 03:46 PM

are you saying =IF('ASP DATA FILE'!Q2="","blank",'ASP DATA FILE'!Q2) this formul is to go in 'ASP DATA FILE'!Q2 or another cell?

**NSTurk725** · 06-20-2009, 04:53 PM

It is to go in another cell. It reads from a different spreadsheet, but I want the column with the formula in it to be updated to a some unique text, if the vlookup value returns blank.

Basically i just wanna autoassign some text+a counter to make it unique.

like Blank1, blank2, blank3, etc.

**martindwilson** · 06-20-2009, 05:02 PM

use row()
=IF('ASP DATA FILE'!Q2="","blank "&row(),'ASP DATA FILE'!Q2)
although it wont be blank 1,blank 2,... it will be unique for that column
or say formula in A1 'IF('ASP DATA FILE'!B1="","blank 1",'ASP DATA FILE'!B1)
formula in A2 dragged down 'IF('ASP DATA FILE'!B2="","blank "&SUMPRODUCT(--(LEFT($A$1:A1,5)="blank"))+1,'ASP DATA FILE'!B2)
will give each blank cell a sequential number