Array working different in two files

J

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
I have had File A (attached) working for years now. It was our timecard. I have removed some information but it doesn't affect my issue. So, I have a macro called Consolidate that would look at each day and the week and at the bottom under Weekly Job Totals, would list each job number individually worked on, then tally the hours for that job number. Columns I through P were hidden on the card and were used to help with array by day.

Now we switched to a new timecard (File B) and I've been making the same codes in the new one so it functions the same but now the array tallies the numbers including the job number. I have matched everything to a 't' that i know of, (font style, type, etc.). Why is it adding my job numbers together? Also, the next column labeled CC in Column Q of File B, I'll need that to not add together also.

Any help?
TIA
 
Last edited by a moderator:
Neither tallies each job number individually that I can see. A tallies them all into 1, B tallies into 2.
 
Bob Phillips said:
Neither tallies each job number individually that I can see. A tallies them all into 1, B tallies into 2.
Click to expand...

Try this updated one. It works for me, but I didn't remove as much data as before, in which case we may be on to something....if I removed something important on the first post but I'm pretty sure I didn't.

Use job numbers 1022000, 1023000, and 1024000 with different combinations of hours. Then at the bottom hit Sign, then Submit (at top.)
 
Last edited by a moderator:
There are no jobs to aggregate there, so apart from a few zeroes, it does nothing.
 
Here is a different copy of the file. In this version, the Submit won't work which is fine but you can at least hit the Sign button and run the Consolidate Macro. If we look at this a different way because it's not working on your end, how would you array the data to achieve a list at the bottom listing each job number during the week and the total Regular hours to that job number?

Thanks for your help on this!
 

Attachments

  • File A1.xlsm
    44.9 KB · Views: 14
Can you please post links to your thread(s) in other forum(s)?
 
I'm still at a loss here. I found myself trying to find another method of tallying hours based on the job number but the original issue is that this array worked on my old timecard, but doesn't on the new one. I don't understand what would have changed. Is there any ideas or new ways to array my job numbers/time?
 
I'm not sure at all, but are you missing something from the end of the consolidate statement?

…Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
instead of:
…Function:=xlSum
 
It wasn't on my original excel file, however I added it to the new file and it worked for the most part! What is each command doing? It worked on my job numbers and the hours, but there's another column of data next to the job number (Column Q "CC") that is still adding when I just need it to list the data once. How can I achieve that?

Thank you for the input!
 
I don't think you'll be able to with Consolidate.
I'd use a Pivot Table; set it up as you want it then when the macro runs, just refresh it in the code.
I don't know what you want to see there but you could put the Job# and CC in the Row Labels area in that order, the (Sum of)RE in the Values area, hide Subtotals (and perhaps Grand Totals), have the Layout in Tabular form. If you don't want later to send the headers etc in the pivot table, it's easy enough just to send the data body part.
 
A pivot table won't work here. What I'm trying to do is, the user enters the job numbers on the left along with hours. The data is transferred over to the right and some other fields added there. The consolidate is used to look at each job number entered and tally the hours but still only listing the job number once. Your code suggestion worked for this as I had it before, but I want to do the same thing for the CC field next to the Job #. So if if the user during the week worked on job 1339000 and CC 198 for 8 hours, then the next day worked on 1339000 and CC 200 for 8 hours, I would want the final data at the bottom to be:

1339000 198 8
1339000 200 8

Thanks for your input!
 
jmerch said:
A pivot table won't work here.
Click to expand...
I'm fairly sure it will!
In the attached find a couple of pivot table examples; I can't see the difference between what you want and what's in the Pivot Table.
 

Attachments

  • File B1.xlsm
    53.9 KB · Views: 12
Ok, I should know better than to say "won't".

If I go with the pivot table, is there a way for it to auto-update? Originally this area where the table is was set to only fill in during a macro when the timesheet was ready to submit.

Is there a way to have the cells stay blank instead of physically saying "(blank)"? Reason being, an end user is going to copy this data and paste it into another workbook and that workflow needs to be very simple. That needs to be the workflow instead of them deleting fields that say (blank).
 
You could refresh on each change of the worksheet:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
PivotTables("PivotTable1").PivotCache.Refresh 'adjust for your pivot table's name.
Application.EnableEvents = True
End Sub

or include the line (or similar):
Code: 
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
in one of your button click events.

I've been trying to get "(blanks)" appearing in the pivot table in the FileB1.xlsm but can't; how are you getting them to show up, and in which column?
 
Columns "Dept", "Pay ID", "Units", and "Receipt".
 

Attachments

  • 2014-04-18 Excel PivotTable.jpg
    2014-04-18 Excel PivotTable.jpg
    51.9 KB · Views: 13
See message #4 here: http://www.excelguru.ca/forums/showthread.php?178-Avoiding-(blank)-in-row-label-fields
I tried it and it works; I selected just one cell in the Pivot Table where "(blank)" appears, entered a space, then Enter and all the blanks in that column disappeared, what's more, the table refreshed properly after the source data was changed.
If you need to undo this, it seems you can't without taking that field out of the table design, then putting it straight back in again - but I doubt you'll ever need to do that.

If you also want to hide those small -/+ buttons you can: Options, Display tab, topmost checkbox: Show expand/collapse buttons, uncheck it.
 
I tried creating a pivot table on a fresh file of the Timesheet. How do you get it to list the column headings horizontally as you did? My table is creating these vertically so I only have one column instead of 7 columns with each heading?
 
I don't understand; you did the thumbnail in msg#16, not me!
Put the fields in the Row Labels section of the pivot.
 
That's the one I got from your sample file. I tried to make a new one and tried to replicate yours but it comes in vertically :(

I did put the fields in the Row Labels Section
 
You must log in or register to reply here.
Back
Top