Thread: Returning null values/cell formatting as criteria?

Hi there,

I have a multiple data sheets with several thousand rows each and I need to do 2 things that I can't figure out a way to do.

I have a qualifier field (the one with "s" values in the attached image).

I need to bring over the other cell field values from the previous and subsequent rows that have an "s" (highlighted section).

The 2 complications are that:

1. I need to be able to drag a formula down the entire sheet without getting any values in the other rows [e.g. IF(cellX="s", yada yada, NULL)] so that I can copy and paste special excluding empty cells later on.

2. Shaded cells indicate a sampling boundary, so I need to exclude some data if an "s" occurs in a row with a shaded data value.

But I can't figure out any syntax that returns an empty cell from an IF statement, nor can I figure a way to use cell formatting as a criteria in an IF statement [e.g. IF(cellX=#shaded?, Y,Z)].

Can anyone tell me

A. Is there is a way to return a null value from a function?
B. Is it possible to use a cell's formatting as part of a function?

Thanks!

Originally Posted by billyellis

Hi there,

I have a multiple data sheets with several thousand rows each and I need to do 2 things that I can't figure out a way to do.

I have a qualifier field (the one with "s" values in the attached image).

I need to bring over the other cell field values from the previous and subsequent rows that have an "s" (highlighted section).

The 2 complications are that:

1. I need to be able to drag a formula down the entire sheet without getting any values in the other rows [e.g. IF(cellX="s", yada yada, NULL)] so that I can copy and paste special excluding empty cells later on.

2. Shaded cells indicate a sampling boundary, so I need to exclude some data if an "s" occurs in a row with a shaded data value.

But I can't figure out any syntax that returns an empty cell from an IF statement, nor can I figure a way to use cell formatting as a criteria in an IF statement [e.g. IF(cellX=#shaded?, Y,Z)].

Can anyone tell me

A. Is there is a way to return a null value from a function?
B. Is it possible to use a cell's formatting as part of a function?

Thanks!

A. I'm of the opinion that such a return value would be nice. That said, I don't know of one. You will probably have to write up a macro to do the calculations you want if you want a null value entered.

B. As a parameter, no. Again, you'll need to go to macros.

---------

You'll probably just want a macro that loops through and populates your spreadsheet with values. If you only want values, this shouldn't be a huge issue. If you want formulas too, you're probably going to have to be more creative.

I don't think the macro would be that complicated -- essentially, you're just looping through a range of rows and putting values into cells that need them. As long as the destination range starts out blank (ie. all null), you don't even have to worry about deleting items.

Something like this... note that this is only approximate. And I'd recommend not using absolute references like I did. (ie. where I have put numbers for the columns, you might want to put relative references or variables so it is more robust):

Sub Process ()
Dim mySheet as Worksheet
Dim startRow as Long
Dim endRow as Long
Dim i as Long

mySheet = Worksheets("Sheet1") 'Or whichever sheet

startRow = 2 'Or you can prompt for them, or use selections, etc
endRow = 100

For i = startRow to endRow
'The color stuff I'm vague on... you'll have to research this
If mySheet.Cells(i, 3) is not coloured Then 'As I said, research this
If i>startRow and mySheet.Cells(i-1,3) is coloured Then
'Get values from previous row.
mySheet.Cells(i,5) = mySheet.Cells(i-1,1)
mySheet.Cells(i,6) = mySheet.Cells(i-1,2)
mySheet.Cells(i,7) = mySheet.Cells(i-1,3)
Elseif i<endRow and mySheet.Cells(i+1,3) is coloured Then
'Get values from next row.
'Similar to previous section, except i+1 instead of i-1
Endif
Endif
Next i
End Sub

Scott

Thanks. But good grief - I was hoping I was missing something obvious like a simple syntax like "IsNull" to set a cell to a No Data value, like there is in other programs (e.g. GIS software). What a pain in the behind. How big an oversight is it to have a spreadsheet with empty cells as a starting point, but no terminology to make occupied cells empty again? Son of a gun...

Thanks for your help, and I will take a close look at the macro template you wrote up.

Hi,

1. =ISBLANK(a1)
will return True if cell is empty ...

2. Is your shading an interior color or the result of conditionnal formatting ?


HTH
Carim

Hi,

In column F you can have initially following formula
=IF(OFFSET(D4,1,0)="s",A4,IF(OFFSET(D4,-1,0)="s",A4,""))

HTH
Carim

Yes Bryan ... You are right ...

Cheers
Carim