|
Function or Formula help - get data from another sheet
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 |
Function or Formula help - get data from another sheet
You can use VLOOKUP() to get a single row matching at criteria, to get
multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
doesn't provide a sample of formula to use not getting it?
"Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
Hi,
You may refer to my article at the following link - http://office.microsoft.com/en-au/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
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" wrote: Hi, You may refer to my article at the following link - http://office.microsoft.com/en-au/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
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" wrote in message ... 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" wrote: Hi, You may refer to my article at the following link - http://office.microsoft.com/en-au/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
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" wrote: 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" wrote in message ... 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" wrote: Hi, You may refer to my article at the following link - http://office.microsoft.com/en-au/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: Hi, You may refer to my article at the following link - http://office.microsoft.com/en-au/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: Hi, You may refer to my article at the following link - http://office.microsoft.com/en-au/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
Function or Formula help - get data from another sheet
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: Hi, You may refer to my article at the following link - http://office.microsoft.com/en-au/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nadine" wrote in message ... 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, "Gary''s Student" wrote: You can use VLOOKUP() to get a single row matching at criteria, to get multiple rows, see: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200906 "Nadine" wrote: 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 |
| All times are GMT +1. The time now is 09:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com