Function or Formula help - get data from another sheet

[Nadine]

I'm not sure if this is possible, it is similar to a DGET but not working.
Example
Sheet 1 - Columns - Area, Address, Date
multiple rows of data, each row represents one client record

Sheet 2
I want all the rows that match an Area number, i.e.
Display
Address and Date for rows that match area 4

Sheet 2 has several different formatted blocks and each block represents an
Area, and want to populate those blocks with the addresses & dates that meet
the Area criteria

i.e. Columns B-E list Area 4 addresses/dates
Columns G-J, list Area 2 addresses/dates
etc...

Don't know if this can be done in excel, trying to avoid having to create an
access database. Because it's like building a report based on the data and
criteria, but that's what trying to do, is build a report listing the data.
Pivot table couldnt' get it to work for me.

Any ideas.
Thanks
Nadine

 

[Gary''s Student]

You can use VLOOKUP() to get a single row matching at criteria, to get
multiple rows, see:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

 

[Nadine]

doesn't provide a sample of formula to use not getting it?

 

[Nadine]

figured out the formula but doesn't give me results needed,
As address is in one column and date is in the next column
and I only get one row
dont' get all rows of addresses and dates that match the area
i.e.

Get list of all addresses and dates for Area 4,

 

[Ashish Mathur]

Hi,

You may refer to my article at the following link -
http://office.microsoft.com/en-au/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Nadine]

Thanks but that was same answer received above doesn't meet my needs.
Thanks

Basically trying to get excel to build a report, Sheet 1 contains all the
date, i.e. area, name, address, date, each column has specific information,
think of maybe an employee table.

I want to extra specific rows of data, ie. all the records for Area 4 and
put it into a a specific table on Sheet 2.

Similar to a filter, but don't want to run an advance filter everytime want
the details, want Sheet 2 to update automatically when data is added to Sheet
1.

Thanks

 

[Ashish Mathur]

Hi,

It is easy enough to auto update the output of an advanced filter (through
VBA). Do let me know if this would be OK with you

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Nadine]

I've never done VBA scripting.

Right now, I'm thinking have to create a macro for each of the advanced
filtering to populate each table, and then I guess have excel run another
macro on open of the document to run all the 10 different macros to create
the 10 different tables.

Unless this can be done easier in VBA.

 

[Ashish Mathur]

Hi,

You could try this different approach.

1. Convert the source range to a list (Ctrl+L)
2. Create a pivot table from the source data - drag Area to the page field
area and name address and date to the row field area
3. Remove the subtotal and grand total rows
4. Now place the cursor anywhere inside the pivot table
5. In Excel 2007, goto Pivottable Tools > Options > Pivot Table > Options >
Show Report Filter pages > OK. In Excel 2003, it is Show Pages on the pivot
table toolbar
6. This will create a separate sheet with data for each single area
7. If you add data to the original source data, updating any one of the many
pivot table worksheets will update all the pivot tables

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Nadine]

That did the trick, I was trying pivot tables but I just wasn't getting it to
work the way I wanted, so gave up on that.
But your notes got it going into the perfect direction for me.

BIG THANKS, BIG SMILE.

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com