First of all, thanks once again for all the help I've been receiving over
the last 2 days.

I have a chart with location(column A) and sales information (rows B to P).
I was asked to prepare a function that would only display the month the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel free
to advise. Thanks once again.

Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook as
your data table. Then use the function like this in cell E2 (reflecting the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving over
the last 2 days.

I have a chart with location(column A) and sales information (rows B to
P).
I was asked to prepare a function that would only display the month the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel
free
to advise. Thanks once again.

Hey Bernie,

Thanks but I have no idea what you're asking me to do. Can you please
digress a bit more? Thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook as
your data table. Then use the function like this in cell E2 (reflecting
the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving
over
the last 2 days.

I have a chart with location(column A) and sales information (rows B to
P).
I was asked to prepare a function that would only display the month the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel
free
to advise. Thanks once again.

Peter,

Sorry, since you were posting to the programming newsgroup I thought you
wanted a programming solution.

Visit both

http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

for more information on how to use User-Defined-Functions.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
Hey Bernie,

Thanks but I have no idea what you're asking me to do. Can you please
digress a bit more? Thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook as
your data table. Then use the function like this in cell E2 (reflecting
the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving
over
the last 2 days.

I have a chart with location(column A) and sales information (rows B
to
P).
I was asked to prepare a function that would only display the month
the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel
free
to advise. Thanks once again.

I do want a programming solution. I just didn't understand what you had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Sorry, since you were posting to the programming newsgroup I thought you
wanted a programming solution.

Visit both

http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

for more information on how to use User-Defined-Functions.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
Hey Bernie,

Thanks but I have no idea what you're asking me to do. Can you please
digress a bit more? Thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook
as
your data table. Then use the function like this in cell E2
(reflecting
the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving
over
the last 2 days.

I have a chart with location(column A) and sales information (rows B
to
P).
I was asked to prepare a function that would only display the month
the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please
feel
free
to advise. Thanks once again.

Peter,

Visit the two links for instructions on how to use the code that I posted
previously.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
I do want a programming solution. I just didn't understand what you had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.

Hey Bernie,

Sorry for bugging you. I went back to your suggestion. After reading it a
few times, i think i understand what you wanted me to do. Anyways, the code
is enter into a module and the formula I've placed in cell e2 (next to the
chart). I get the #name? error when I press enter. I've retyped it a few
times, even copied and paste what you typed but to no avail. Any help is
appreciated, thanks.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Visit the two links for instructions on how to use the code that I posted
previously.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
I do want a programming solution. I just didn't understand what you had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.

Peter,

I will send you a working version to your hotmail address.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
Hey Bernie,

Sorry for bugging you. I went back to your suggestion. After reading it
a
few times, i think i understand what you wanted me to do. Anyways, the
code
is enter into a module and the formula I've placed in cell e2 (next to the
chart). I get the #name? error when I press enter. I've retyped it a few
times, even copied and paste what you typed but to no avail. Any help is
appreciated, thanks.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Visit the two links for instructions on how to use the code that I
posted
previously.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
I do want a programming solution. I just didn't understand what you
had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.