Return Side and Header information from table?
 

I have a spreadsheet that has addresses down column A and Types of
inspections across Row 1. The rest of the table is filled with the dates
that the inspections have passed. I need to be able to generate a list of
the inspections that passed for a given month. That list should include the
address, the inspection type and the date the inspection passed. For a given
month there may be multiple inspections that passed for a single address. I
would like them reported in individual lines. Using index, small and IF I am
able to get part of it done, but I just cant seem to get all the data to
report in the same order.

Any help is greatly appreciated.
Steve

Return Side and Header information from table?
 

What about - Is there a way to return the address (row, column) of a cell?
for example I can use this formula to find all the cells who match the month
I want and return the row that it is in. Or I can use it to return the
column that it is in but they return results in different orders so I can't
pair the row and column together.


=SMALL(IF(MONTH($C$5:$E$12)=5,ROW($C$5:$E$12)),ROW (1:1))

Where C5-E12 is my sample range. Then I could use the returned data to get
the row and column header information that I am looking for.

Thanks
Steve


"SteveT" wrote:

I have a spreadsheet that has addresses down column A and Types of
inspections across Row 1. The rest of the table is filled with the dates
that the inspections have passed. I need to be able to generate a list of
the inspections that passed for a given month. That list should include the
address, the inspection type and the date the inspection passed. For a given
month there may be multiple inspections that passed for a single address. I
would like them reported in individual lines. Using index, small and IF I am
able to get part of it done, but I just cant seem to get all the data to
report in the same order.

Any help is greatly appreciated.
Steve

Return Side and Header information from table?
 

Can you post a screencap of what your data looks like and what you are
trying to do?

You can upload (for free) screencaps at imageshack.us and then post the link
to that screencap.

Biff

"SteveT" wrote in message
...
What about - Is there a way to return the address (row, column) of a cell?
for example I can use this formula to find all the cells who match the
month
I want and return the row that it is in. Or I can use it to return the
column that it is in but they return results in different orders so I
can't
pair the row and column together.


=SMALL(IF(MONTH($C$5:$E$12)=5,ROW($C$5:$E$12)),ROW (1:1))

Where C5-E12 is my sample range. Then I could use the returned data to
get
the row and column header information that I am looking for.

Thanks
Steve


"SteveT" wrote:

I have a spreadsheet that has addresses down column A and Types of
inspections across Row 1. The rest of the table is filled with the dates
that the inspections have passed. I need to be able to generate a list
of
the inspections that passed for a given month. That list should include
the
address, the inspection type and the date the inspection passed. For a
given
month there may be multiple inspections that passed for a single address.
I
would like them reported in individual lines. Using index, small and IF
I am
able to get part of it done, but I just cant seem to get all the data to
report in the same order.

Any help is greatly appreciated.
Steve

Return Side and Header information from table?
 

Here is the screencap of my file.

http://www.tpibcc.com/excel/screencap.jpg

There are a total of about 20 different inspection types and around 800
different addresses and I need to be able to track what inspecitons have
passed for a given month.

Thanks
Steve

"T. Valko" wrote:

Can you post a screencap of what your data looks like and what you are
trying to do?

You can upload (for free) screencaps at imageshack.us and then post the link
to that screencap.

Biff

"SteveT" wrote in message
...
What about - Is there a way to return the address (row, column) of a cell?
for example I can use this formula to find all the cells who match the
month
I want and return the row that it is in. Or I can use it to return the
column that it is in but they return results in different orders so I
can't
pair the row and column together.


=SMALL(IF(MONTH($C$5:$E$12)=5,ROW($C$5:$E$12)),ROW (1:1))

Where C5-E12 is my sample range. Then I could use the returned data to
get
the row and column header information that I am looking for.

Thanks
Steve


"SteveT" wrote:

I have a spreadsheet that has addresses down column A and Types of
inspections across Row 1. The rest of the table is filled with the dates
that the inspections have passed. I need to be able to generate a list
of
the inspections that passed for a given month. That list should include
the
address, the inspection type and the date the inspection passed. For a
given
month there may be multiple inspections that passed for a single address.
I
would like them reported in individual lines. Using index, small and IF
I am
able to get part of it done, but I just cant seem to get all the data to
report in the same order.

Any help is greatly appreciated.
Steve

Return Side and Header information from table?
 

That's not going to be easy!

Let me play around and see if I can come up with something. No guarantees!

Biff

"SteveT" wrote in message
...
Here is the screencap of my file.

http://www.tpibcc.com/excel/screencap.jpg

There are a total of about 20 different inspection types and around 800
different addresses and I need to be able to track what inspecitons have
passed for a given month.

Thanks
Steve

"T. Valko" wrote:

Can you post a screencap of what your data looks like and what you are
trying to do?

You can upload (for free) screencaps at imageshack.us and then post the
link
to that screencap.

Biff

"SteveT" wrote in message
...
What about - Is there a way to return the address (row, column) of a
cell?
for example I can use this formula to find all the cells who match the
month
I want and return the row that it is in. Or I can use it to return the
column that it is in but they return results in different orders so I
can't
pair the row and column together.


=SMALL(IF(MONTH($C$5:$E$12)=5,ROW($C$5:$E$12)),ROW (1:1))

Where C5-E12 is my sample range. Then I could use the returned data to
get
the row and column header information that I am looking for.

Thanks
Steve


"SteveT" wrote:

I have a spreadsheet that has addresses down column A and Types of
inspections across Row 1. The rest of the table is filled with the
dates
that the inspections have passed. I need to be able to generate a
list
of
the inspections that passed for a given month. That list should
include
the
address, the inspection type and the date the inspection passed. For
a
given
month there may be multiple inspections that passed for a single
address.
I
would like them reported in individual lines. Using index, small and
IF
I am
able to get part of it done, but I just cant seem to get all the data
to
report in the same order.

Any help is greatly appreciated.
Steve