If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
revised q on report from tables
Hi
Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN |
#2
|
|||
|
|||
revised q on report from tables
Hi
If all the data is now in one list, then DataFilterAutofilter Select the PC required from the PC column, and all related data will be visible. -- Regards Roger Govier UKMAN wrote: Hi Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN |
#3
|
|||
|
|||
revised q on report from tables
Hi,
I'm assuming that you have your drop-down in A1 on the report sheet, and you want a list of multiple items to be brought from Sheet1!$B$2:$G$100 to Sheet2, filtered by the value in A1. This can be done without any figures in column A, using an array formula, but the following method should be easier to understand and modify to your own requirements: In A2: =MATCH($A$1,Sheet1!$A$2:$A$100,0) In A3: =MATCH($A$1,OFFSET(AutoFilter!$A$2:$A$100,$A2,),0) +$A2 In B2: =INDEX(AutoFilter!B$2:B$100,$A2) Copy B2 across and down B2:G3 Then copy A3:G3 down as far as required. HTH Steve. "UKMAN" wrote in message ... Hi Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN |
#4
|
|||
|
|||
revised q on report from tables
Roger,
would use filtering but (a) the report is preformat i.e. text surrounds (b) the tables are on a seperate sheet. Many thanks anyway Ukman "Roger Govier" wrote: Hi If all the data is now in one list, then DataFilterAutofilter Select the PC required from the PC column, and all related data will be visible. -- Regards Roger Govier UKMAN wrote: Hi Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN . |
#5
|
|||
|
|||
revised q on report from tables
Steve,
many thanks for your help. after removing the autofilter I got it to work on the same sheet but couldn't work across sheets? Autofilter gave errors??? The other problem was that when a blank row in the table it just repeated the first record?? Below is acopy & paste of the table. it goes on for 983 rows and the Project code goes from PC01 to PC20. Hope this helps. The lookup shows the Proj Code for selection. Proj Code Name Charge B S L1 NB PC01 colin $400.00 $1.00 $0.50 $0.50 PC01 fred jones $200.00 $1.00 hope this helps Many thanks "Steve Dunn" wrote: Hi, I'm assuming that you have your drop-down in A1 on the report sheet, and you want a list of multiple items to be brought from Sheet1!$B$2:$G$100 to Sheet2, filtered by the value in A1. This can be done without any figures in column A, using an array formula, but the following method should be easier to understand and modify to your own requirements: In A2: =MATCH($A$1,Sheet1!$A$2:$A$100,0) In A3: =MATCH($A$1,OFFSET(AutoFilter!$A$2:$A$100,$A2,),0) +$A2 In B2: =INDEX(AutoFilter!B$2:B$100,$A2) Copy B2 across and down B2:G3 Then copy A3:G3 down as far as required. HTH Steve. "UKMAN" wrote in message ... Hi Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN |
#6
|
|||
|
|||
revised q on report from tables
Oops, forgot to change the sheet name in the second and third formulae (I
was working with a sheet called AutoFilter). Should have been: In A2: =MATCH($A$1,Sheet1!$A$2:$A$1000,0) In A3: =MATCH($A$1,OFFSET(Sheet1!$A$2:$A$1000,$A2,),0)+$A 2 In B2: =INDEX(Sheet1!B$2:B$1000,$A2) Sorry about that. Can't see a reason why blank rows would cause a problem. If you'd like to e-mail your file to me, I'll take a look at it. "UKMAN" wrote in message ... Steve, many thanks for your help. after removing the autofilter I got it to work on the same sheet but couldn't work across sheets? Autofilter gave errors??? The other problem was that when a blank row in the table it just repeated the first record?? Below is acopy & paste of the table. it goes on for 983 rows and the Project code goes from PC01 to PC20. Hope this helps. The lookup shows the Proj Code for selection. Proj Code Name Charge B S L1 NB PC01 colin $400.00 $1.00 $0.50 $0.50 PC01 fred jones $200.00 $1.00 hope this helps Many thanks "Steve Dunn" wrote: Hi, I'm assuming that you have your drop-down in A1 on the report sheet, and you want a list of multiple items to be brought from Sheet1!$B$2:$G$100 to Sheet2, filtered by the value in A1. This can be done without any figures in column A, using an array formula, but the following method should be easier to understand and modify to your own requirements: In A2: =MATCH($A$1,Sheet1!$A$2:$A$100,0) In A3: =MATCH($A$1,OFFSET(AutoFilter!$A$2:$A$100,$A2,),0) +$A2 In B2: =INDEX(AutoFilter!B$2:B$100,$A2) Copy B2 across and down B2:G3 Then copy A3:G3 down as far as required. HTH Steve. "UKMAN" wrote in message ... Hi Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN |
#7
|
|||
|
|||
revised q on report from tables
Steve,
Absolutly no problem as I made an error in my copying down i.e. a2 I didn't copy down with the other cells I have now moved the formulas to the report on a differant sheet and works great. One question though, due to the way the data is collated in the report it will have blank lines between records in some circumstances so could the code be modified so to ignore blank rows?? If not no worries but many thanks for your fanastic help. You made a hard problem sound easy to resolve. Cheers UKMAN "Steve Dunn" wrote: Oops, forgot to change the sheet name in the second and third formulae (I was working with a sheet called AutoFilter). Should have been: In A2: =MATCH($A$1,Sheet1!$A$2:$A$1000,0) In A3: =MATCH($A$1,OFFSET(Sheet1!$A$2:$A$1000,$A2,),0)+$A 2 In B2: =INDEX(Sheet1!B$2:B$1000,$A2) Sorry about that. Can't see a reason why blank rows would cause a problem. If you'd like to e-mail your file to me, I'll take a look at it. "UKMAN" wrote in message ... Steve, many thanks for your help. after removing the autofilter I got it to work on the same sheet but couldn't work across sheets? Autofilter gave errors??? The other problem was that when a blank row in the table it just repeated the first record?? Below is acopy & paste of the table. it goes on for 983 rows and the Project code goes from PC01 to PC20. Hope this helps. The lookup shows the Proj Code for selection. Proj Code Name Charge B S L1 NB PC01 colin $400.00 $1.00 $0.50 $0.50 PC01 fred jones $200.00 $1.00 hope this helps Many thanks "Steve Dunn" wrote: Hi, I'm assuming that you have your drop-down in A1 on the report sheet, and you want a list of multiple items to be brought from Sheet1!$B$2:$G$100 to Sheet2, filtered by the value in A1. This can be done without any figures in column A, using an array formula, but the following method should be easier to understand and modify to your own requirements: In A2: =MATCH($A$1,Sheet1!$A$2:$A$100,0) In A3: =MATCH($A$1,OFFSET(AutoFilter!$A$2:$A$100,$A2,),0) +$A2 In B2: =INDEX(AutoFilter!B$2:B$100,$A2) Copy B2 across and down B2:G3 Then copy A3:G3 down as far as required. HTH Steve. "UKMAN" wrote in message ... Hi Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN |
#8
|
|||
|
|||
revised q on report from tables
I can't see why there would be a problem with blank rows. Would you like to
send your workbook to me, so I can investigate more directly? "UKMAN" wrote in message ... Steve, Absolutly no problem as I made an error in my copying down i.e. a2 I didn't copy down with the other cells I have now moved the formulas to the report on a differant sheet and works great. One question though, due to the way the data is collated in the report it will have blank lines between records in some circumstances so could the code be modified so to ignore blank rows?? If not no worries but many thanks for your fanastic help. You made a hard problem sound easy to resolve. Cheers UKMAN "Steve Dunn" wrote: Oops, forgot to change the sheet name in the second and third formulae (I was working with a sheet called AutoFilter). Should have been: In A2: =MATCH($A$1,Sheet1!$A$2:$A$1000,0) In A3: =MATCH($A$1,OFFSET(Sheet1!$A$2:$A$1000,$A2,),0)+$A 2 In B2: =INDEX(Sheet1!B$2:B$1000,$A2) Sorry about that. Can't see a reason why blank rows would cause a problem. If you'd like to e-mail your file to me, I'll take a look at it. "UKMAN" wrote in message ... Steve, many thanks for your help. after removing the autofilter I got it to work on the same sheet but couldn't work across sheets? Autofilter gave errors??? The other problem was that when a blank row in the table it just repeated the first record?? Below is acopy & paste of the table. it goes on for 983 rows and the Project code goes from PC01 to PC20. Hope this helps. The lookup shows the Proj Code for selection. Proj Code Name Charge B S L1 NB PC01 colin $400.00 $1.00 $0.50 $0.50 PC01 fred jones $200.00 $1.00 hope this helps Many thanks "Steve Dunn" wrote: Hi, I'm assuming that you have your drop-down in A1 on the report sheet, and you want a list of multiple items to be brought from Sheet1!$B$2:$G$100 to Sheet2, filtered by the value in A1. This can be done without any figures in column A, using an array formula, but the following method should be easier to understand and modify to your own requirements: In A2: =MATCH($A$1,Sheet1!$A$2:$A$100,0) In A3: =MATCH($A$1,OFFSET(AutoFilter!$A$2:$A$100,$A2,),0) +$A2 In B2: =INDEX(AutoFilter!B$2:B$100,$A2) Copy B2 across and down B2:G3 Then copy A3:G3 down as far as required. HTH Steve. "UKMAN" wrote in message ... Hi Would still love help on previous regarding 20 tables but in a panic. I have now ref all tables to 1 long list so as a quick fix: the col headings of the list are PC | Name | Rate | B | S |X | L1 In the report sheet I have a drop down list of the PC col. I would like to be able to select the PC and the report be filled with the following data only: | Name | Rate | B | S |X | L1 I have used vlookup and tried to hack an old code that can be copied down but it will only ever show the first record Sorry for being thick.. Cheers for all help and I am learning UKMAN |
Thread Tools | |
Display Modes | |
|
|