Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Such theatrics,
Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Shows invoice to be applied, 2nd list) Shows what has been applied that are €śon-account€ť. Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the €śon-account€ť data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and €śon-account€ť data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. Im not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
On Oct 16, 5:33 pm, pgarcia wrote:
Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Thanks JW.
Data list 1st list: GL Date Batch Name Difference Status Responsible 1-Oct-07 ML-014-011007-49 $321.15 Warning David 1-Oct-07 ML-015-011007-50 $153.01 Warning David 1-Oct-07 ML-017-011007-52 $100.00 Warning David 1-Oct-07 ML-023-011007-58 $4.85 Warning David 4-Oct-07 1MLCLSAMEX100107 $2,336.30 Warning Betsy 4-Oct-07 2MLCLSAMEX100107 $206.40 Warning Betsy 2-Oct-07 ML-006-021007-32 $150.00 Warning Tally 2-Oct-07 ML-008-021007-34 $262.78 Warning Tally 2-Oct-07 ML-006-021007-6 $63.24 Warning David 2nd list: Customer# GL Date On Account $ Days Old Responsible 13449170 10-OCT-07 $4,934.08 7 Jackie 12858370 14-DEC-06 $2,304.00 309 Jenni 222196855 26-SEP-07 $1,980.65 28 Jackie 702319450 12-DEC-06 $1,551.00 309 Jenni 252432972 28-JUL-07 $1,408.10 85 David 252350184 28-FEB-07 $1,167.07 232 Jackie 292526500 15-AUG-07 $1,085.65 63 David 43155965 05-SEP-07 $1,065.38 42 Julio 222199552 16-MAR-07 $983.12 217 David And this is what it should look like 1st tab: GL Date Batch Name Difference Status Responsible 1-Oct-07 ML-014-011007-49 $321.15 Warning David 1-Oct-07 ML-015-011007-50 $153.01 Warning David 1-Oct-07 ML-017-011007-52 $100.00 Warning David 1-Oct-07 ML-023-011007-58 $4.85 Warning David 2-Oct-07 ML-006-021007-6 $63.24 Warning David Customer# GL Date On Account $ Days Old Responsible 252432972 28-JUL-07 $1,408.10 85 David 292526500 15-AUG-07 $1,085.65 63 David 222199552 16-MAR-07 $983.12 217 David 2nd tab: GL Date Batch Name Difference Status Responsible 4-Oct-07 1MLCLSAMEX100107 $2,336.30 Warning Betsy 4-Oct-07 2MLCLSAMEX100107 $206.40 Warning Betsy Customer# GL Date On Account $ Days Old Responsible no data tabs 3,4,5 ect. Hope that make sence. Thanks "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Any luck yet?
"JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
pgarcia,
How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
When click on a "Button", please. But I can creat that. Would this be a VB
code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gods of VB, I call on thy.
Assuming that your sheets are named as follows: "Invoice", "On
Account", "David", "Betsy", "Tally", "Julio" "Jackie" STEP 1: Create a Module and add the following code: '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' Sub Update_Data() Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer Dim strEmployee as string ' ' ' ' Get Invoice Data First ' ' ' ' Copy and Paste Header Sheets("Invoice").Select Range("A1:A5").Select Sheets("David").Select Range("A1").Select Activesheet.Paste Sheets("Betsy").Select Range("A1").Select Activesheet.Paste Sheets("Tally").Select Range("A1").Select Activesheet.Paste Sheets("Julio").Select Range("A1").Select Activesheet.Paste Sheets("Jackie").Select Range("A1").Select Activesheet.Paste Sheets("Invoice").Select ' ' ' ' Update the Invoice data on the Employe sheets i_david=2 i_betsy=2 i_tally=2 i_julio=2 i_jackie=2 i=2 While Sheets("Invoice").Cells(i,1) < "" Select Case Sheets("Invoice").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("Invoice").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1) Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2) Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3) Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4) Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5) i=i+1 Wend ' ' ' ' Get On Account Data ' ' ' ' Copy and Paste Header Sheets("On Account").Select Range("A1:A5").Select Sheets("David").Select Range(Cells(i_david+2,1)).Select Activesheet.Paste Sheets("Betsy").Select Range(Cells(i_betst+2,1)).Select Activesheet.Paste Sheets("Tally").Select Range(Cells(i_tally+2,1)).Select Activesheet.Paste Sheets("Julio").Select Range(Cells(i_julio+2,1)).Select Activesheet.Paste Sheets("Jackie").Select Range(Cells(i_jackie+2,1)).Select Activesheet.Paste Sheets("On Account").Select ' ' ' ' Update the On Account data on the Employe sheets i_david=i_david+3 i_betsy=i_betsy+3 i_tally=i_tally+3 i_julio=i_julio+3 i_jackie=i_jackie+3 i=2 While Sheets("On Account").Cells(i,1) < "" Select Case Sheets("On Account").Cells(i,5) Case "David" ii=i_david i_david=i_david+1 Case "Betsy" ii=i_betsy i_betsy=i_betsy+1 Case "Tally" ii=i_tally i_tally=i_tally+1 Case "Julio" ii=i_julio i_julio=i_julio+1 Case "Jackie" ii=i_jackie i_jackie=i_jackie+1 End Select strEmployee=Sheets("On Account").Cells(i,5).value Sheets(strEmployee).Cells(ii,1)=Sheets("On Account").Cells(i, 1) Sheets(strEmployee).Cells(ii,2)=Sheets("On Account").Cells(i, 2) Sheets(strEmployee).Cells(ii,3)=Sheets("On Account").Cells(i, 3) Sheets(strEmployee).Cells(ii,4)=Sheets("On Account").Cells(i, 4) Sheets(strEmployee).Cells(ii,5)=Sheets("On Account").Cells(i, 5) i=i+1 Wend End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' STEP 2 Create a Button in your spreadsheet and assign it to the macro Good Luck Faisal... On Oct 31, 4:39 pm, pgarcia wrote: When click on a "Button", please. But I can creat that. Would this be a VB code? "Faisal..." wrote: pgarcia, How do you want to populate this data? When you open the spreadsheet? When you make a change? When you click a button? Give us more detail. Faisal... On 30 Oct, 16:01, pgarcia wrote: Any luck yet? "JW" wrote: On Oct 16, 5:33 pm, pgarcia wrote: Such theatrics, Spread sheet with 5 tabs. The five tabs a equal to 5 employees. All the tabs will be setup the same. Two list; 1st list) Show's invoice to be applied, 2nd list) Shows what has been applied that are "on-account". Each list changes daily, what I would like to do is the following: Have two input tabs, 1) has the invoice data, 2) has the "on-account" data, both have lists contains the person name. The name may appear several times but the data will change daily. The list of names is based off the following formula: =INDEX('Cash App Schedule'!$B$2:$Q$21,MATCH(B2,'Cash App Schedule'!$A$2:$A$21,0),MATCH(D2,'Cash App Schedule'!$B$1:$Q$1,0)) I would like to populate two list on the tab with the invoice data and "on-account" data. I tried using Vlookup, but it was limited. It worked for the first tab, first name, but the 2nd tab, 2nd name, it did not work properly. I had to start at the end of the first name and as you can understand, the lists change daily and will not have the same amount of entrees. This is what I do now, for a list with this information, I filter the name and cut and paste them in to the tab. I'm not able to use a Pivot table as I know the employees will not know how to use it. Thanks in advance, a loyal follower Hi Paul. This one is difficult to understand without seeing a "picture". Could you post an example of your data structure and an example of the desired outcome?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Max or Ron can answer this they are Gods! | Excel Worksheet Functions | |||
I'm not sure what you'd call it, but is it possible to do this? | Excel Discussion (Misc queries) | |||
Call Center Management: How to calculate 'cost per call' | Excel Discussion (Misc queries) | |||
DLL Call | Excel Programming | |||
call sub | Excel Programming |