Gods of VB, I call on thy.

P

pgarcia

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
 
J

JW

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
Click to expand...

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?
 
P

pgarcia

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
 
P

pgarcia

Any luck yet?

JW said:
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?
Click to expand...
 
F

Faisal...

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...
 
F

Faisal...

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...
 
P

pgarcia

Hello,
The VB code stops at the following line:
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)
Thanks
 
F

Faisal...

What is the value of strEmployee? Put the cursor on strEmployee when
it stops.
 
F

Faisal...

I think that your data layout must be different from the tables you
have posted. I assumed that employee name would be in the 5th
column.

in the line:
strEmployee = Sheets("Invoice").Cells(i, 5).Value

Update the 5 to another figure referring to the column number of where
the employee name is stored.

Faisal ...
 
P

pgarcia

Hello again. It stoped in the same spot, but this time strEmployee = "Tally".
Aslo, the first part of the does not see to be working, the pasted header
part, but that's alright. It is picking up the infomation and pasteting it,
but it also paste some part of the VB code. Wierd. FYI, it picked up all of
Daves infomation by the way. Do you want me to email the spread sheet to you?
 
P

pgarcia

Ok, I formated "Tally" to be the same as the rest of the list. For some
reson, that worked. But now it hangs up at the following line:
Sheets("David").Select
Range(Cells(i_david + 2, 1)).Select <----
ActiveSheet.Paste

Aslo, I just added Selection.Copy to the header postion and that did the
trick.

Thanks!
 
P

pgarcia

Hello again.
I think I figered this out. I was a formating issue. This code is very
sensitve. There was a stange space in font of the name. It's fixed and the
code runs great. I did howeve modifyed it a bit. (see comment below the VB
code)

Sub Update_Data()
Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer
Dim strEmployee As String

' ' ' ' Update the Invoice data on the Employe sheets
i_david = 19
i_betsy = 19
i_tally = 19
i_julio = 19
i_jackie = 19
i_jenni = 19

i = 2
While Sheets("Invoice").Cells(i, 1) <> ""
Select Case Sheets("Invoice").Cells(i, 8)
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
Case "Jenni"
ii = i_jenni
i_jenni = i_jenni + 1
End Select
strEmployee = Sheets("Invoice").Cells(i, 8).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)
Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6)
Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7)
i = i + 1
Wend


' ' ' ' Update the On Account data on the Employe sheets
i_david = 79
i_betsy = 38
i_tally = 59
i_julio = 59
i_jackie = 79
i_jenni = 47

i = 2
While Sheets("On Account").Cells(i, 1) <> ""
Select Case Sheets("On Account").Cells(i, 10)
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
Case "Jenni"
ii = i_jenni
i_jenni = i_jenni + 1
End Select
strEmployee = Sheets("On Account").Cells(i, 10).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)
Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6)
Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7)
Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8)
Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9)
i = i + 1
Wend

End Sub


Thanks, that helped a lot.
Question, could this code be modify to select a name? Meaning. With a drop
down list or inputing the name in a cell, could this return the same info.
This would be for another project.
 
F

Faisal...

It's good that it worked out for you. When you mean select name, do
you mean to select a name and do the (same) operation (as in this
code) only for this name?

You will have to trim the code down and maybe use a userform.

Faisal...
 
P

pgarcia

Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select
a station from a drop down menu or have the user input it. It they input the
station code then there should be message indication that there was not
match.

Thanks
 
C

chip.gorman

Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select
a station from a drop down menu or have the user input it. It they input the
station code then there should be message indication that there was not
match.

Thanks




...

read more »
Click to expand...

If you have a lot of stations to deal with then I might take a
different approach than the VBA that is hard coded to each employee.

I just did one of these for you, so I'll describe how I set it up
using only formulas and lookups rather than VBA.

'Data' Tab
Row 1 is your headings, A2:E10 is the data you provided.

'Result' tab

A1 contains the name of the employee to lookup (David, for example.)

A2: =IF(ISERROR(MATCH($A$1,OFFSET(Data!$E$2,B1,0,100,1),0)),"",MATCH($A
$1,OFFSET(Data!$E$2,B1,0,100,1),0))

B2: =IF(ISERROR(+B1+A2),"",+B1+A2)

C2: =IF(ISERROR(INDEX(Data!A$2:A$10,$B2)),"",INDEX(Data!A$2:A$10,$B2))

Copy C2 across to column G and format each column appropriately.

A2 find the first instance of the name AFTER the last time it found
the name. (The OFFSET does that).

Copy-paste row 2 down to whatever the maximum number of rows you are
likely to have for a single employee in a day. The ISERROR portions
will find when the formula "stops finding" David and prevent it from
showing #NAs or #VALUEs.

As for your last question, I think you want to have a pulldown with
the list of stations and the option to override it. Again, it CAN be
accomplished w/o VBA, though it would be more elegant and foolproof
with it. Depending on who wants to use it, make your decision
accordingly.

Set up a pulldown menu with:
Input range = I2:I29 (or whereever you want it and as large as
you need)
Cell link of I1

Put an "override" cell in K1 (where they can type in a name/site
instead of using the pulldown).

In A1 (where "David" was typed in before) put:

=IF(K2="",INDEX(I2:I29,I1),IF(ISNA(MATCH(K2,I2:I29,0)),"Station Not
Found--use the Menu or try again",K2))

A small amount of code assigned to the pulldown could be used to wipe
out the override cell when a selection is made with the dropdown.

I think this accomplishes what you're trying to do. I'd be happy to
send along a copy of this in a workbook if you email me.
 
P

pgarcia

Thank for your effort, but it did not work. The new data does not have
employee names but station codes, e.g. LAX, GGC, JFK ect. The data is from
D2:W2 (22 columns of data) and has around 2879 rows, which changes on a daily
bases. What Faisal has given me, I thought it could be modified to lookup the
station code (I'm thinking, yes, an input cell) and return the 22 lines of
data, where LAX may be repeated 28 to 35 times (in today’s data LAX is listed
282 times). Since it changes daily, I don't want to worry about copying a
formula down to a certain cell or row.

Thank you
 
C

chip.gorman

Right, what I gave you was an example of the approach, not The
Solution.

You can make the "result" formulas go down 2000 rows or more if you
want (whatever the max might ever be), and the results will only
display as far as there is data for that station (which I assumed you
were using "name" as a surrogate for).

As far as the amount of data changing daily on the Data tab, you can
make the match formulas look up the whole way down the column from 2
through 65536. Or look up how to create dynamic named ranges and use
those to account for the varied amount of input data.
 
P

pgarcia

Could you email me the sample? I can't seem to get my to work or should I
send it to you?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using filters for a scheduling worksheet 0
Macro to automatically hide rows based on 3 cells 2
Automatic tab naming 2
Little Problem, Difficult and Hard. 2
Data validation of unique entries using VB 2
VB Code to save a copy of the workbook as another name 2
Insert data on sub-header change 1
Insert file and tab name from list into formula 3

Top