• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data from various sheets onto a master

I think my silly sense of humour has offended you :(
Of course this wasn't my intention.

I have rambled on and not explained myself very well.
If you are have forgiven me shall I start again and explain in better detail??
 
Hi, emmatm!
Absolutely not. I won't ever forgive you. Unless we're talking about three six-pack of Carlsberg! :DD
I was just joking, I apologize for my non-native English if it makes me sound a bit different from my intention. It won't happen again...
Regards!
PS: ... until next time.
 
You can have as many six packs of Carlsberg as you like ;)

I am going to attempt to explain again the scenario on how I see the process should work, if you have a better idea let me know.

  1. We receive the JLL MAC ID, every day this is longer with new jobs added
  2. This is copied onto the first tab on the 'MAC TRACKER' workbook
  3. I would like to read the new lines only, look at what the project type is in column A, then copy the line from columns C (location) - Q (Move Qty) to the tab that has the same name as the project type. For example row 91 column A is 'tech & crate' so copy the line from C-Q onto the 'tech & crate' tab.
So does that make sense so far? I would add a button that could run this bit of coding on its own.
People will now want to go into this workbook and complete columns of data against each job and change statuses etc.

Next step is the code that you have kind of already done for me:

  1. On the MAC TRACKER workbook, in tabs 'projects', 'tech & crate', 'tech only', 'crate only' & 'OH', look at the status in column P and copy the 'scheduled' status in column P onto workbook 'MAC ADMIN' 'SCHEDULED' tab. This should be done for 'COMPLETE' as well.
  2. All 5 tabs on the MAC TRACKER 'projects', 'tech & crate', 'tech only', 'crate only' & 'OH' should be copied onto the 'ALL' tab on the 'MAC ADMIN' workbook.
  3. When these are copied over any columns hidden should be unhidden.
Again I would add a further button to run that bit of coding I am hoping that this is clearer than what I last wrote??
This file will now allow the admin person to review all the jobs that are in scheduled or complete phase

The 3rd file for client review I am still working on the layout of how this will be presented.

Thanks for being so patient and :awesome:
 
Hi, emmatm!

<my comments in this format>

You can have as many six packs of Carlsberg as you like ;)
<dully notified>
I am going to attempt to explain again the scenario on how I see the process should work, if you have a better idea let me know.

  1. We receive the JLL MAC ID, every day this is longer with new jobs added <file? format? how many people?>
  2. This is copied onto the first tab on the 'MAC TRACKER' workbook <manually?>
  3. I would like to read the new lines only, <how do you identify them? by col C JLL MAC ID no? and if any other data of yet existing ones has changed?> look at what the project type is in column A, then copy the line from columns C (location) - Q (Move Qty) to the tab that has the same name as the project type. For example row 91 column A is 'tech & crate' so copy the line from C-Q onto the 'tech & crate' tab.
So does that make sense so far? I would add a button that could run this bit of coding on its own. <1 to 3 of above is what you call "bit of coding"... gonna hire you for bit coding for me!>
People will now want to go into this workbook and complete columns of data against each job and change statuses etc. <is this different from what I asked in last part of 3?>

Next step is the code that you have kind of already done for me:

  1. On the MAC TRACKER workbook, in tabs 'projects', 'tech & crate', 'tech only', 'crate only' & 'OH', look at the status in column P and copy the 'scheduled' status in column P onto workbook 'MAC ADMIN' 'SCHEDULED' tab. This should be done for 'COMPLETE' as well. <no IN PROGRESS here?>
  2. All 5 tabs on the MAC TRACKER 'projects', 'tech & crate', 'tech only', 'crate only' & 'OH' should be copied onto the 'ALL' tab on the 'MAC ADMIN' workbook.
  3. When these are copied over any columns hidden should be unhidden. <like now, that data in target workbook is unhidden?>
Again I would add a further button <this would be rather expensive, maybe unaffordable, are you sure?> to run that bit of coding I am hoping that this is clearer than what I last wrote??
This file will now allow the admin person to review all the jobs that are in scheduled or complete phase

The 3rd file for client review I am still working on the layout of how this will be presented. <then I'd be able to change my yatch, my plane, not sure if that little island at the Caribbean...>

Thanks for being so patient and :awesome: <vade retro, Satan!... do I actually look like... ehmmm... you know...>

Regards!
 
Ok so:
  1. 1 person sends this it is in excel and is exactly the same as the tab labelled JLL Mac ID's on the MAC TRACKER workbook.
  2. This is a simple copy and paste job, copy the whole tab onto the JLL MAC ID's tab
  3. Yes you can identify the new jobs by the JLL MAC ID no, all existing jobs on the sheet will not change (please note that there is now a sequence of jobs in this column being MAC/### however old items were using a 4 digit number)
People will now want to go into this workbook and complete columns of data against each job and change statuses etc. <is this different from what I asked in last part of 3?>
No the team will now go into the tabs and fill out columns P - AF (the blue headers), the grey headers should be copied from the tab as mentioned above.
 
Too premature with post reply :oops:

  1. Don't need 'In progress' anymore
3. Yes exactly like now (thanks for doing that btw ;))

So are we swapping the Carlsberg for a yacht now?
Emoji said he was 'awesome' - I couldn't possibly comment if you look like him, you don't have a photo in you profile.
 
Hi, emmatm!
Who talked about swapping? Just adding... :p and not only a yatch :DD
Yes, you answered all, let me process them. Quite funny? Sure? Nahhh... I'm the most serious person you can find at this site... person from my country, I mean.
Regards!
 
Hi, emmatm!

Then the whole process would be like this?

a) you have 2 workbooks:
MAC TRACKER.xlsm
MAC ADMIIN.xlsx

b) you receive daily a workbook with the JLL MAC ID:
JLL MAC ID.xlsx?
JLL MAC ID's worksheet? More worksheets? If so, what to do with them?

c) you want to update MAC TRACKER.xlsm worksheet JLL MAC ID's with the new lines accordingly to column E JLL MAC No
just adding lines for non-existing codes
no updating of any data for existing codes

d) then each person completes and updates MAC TRACKER.xlsm worksheet JLL MAC ID's with the data they have
all people receive the same daily file?
what if more than one person updates same JLL MAC No?

e) later you want to refresh (copy replacing existing data) worksheets All, Scheduled and Completed with the code of the previous workbook JLL MAC DATA FILE.xlsm file
worksheets from MAC TRACKER.xlsm workbook?
or from MAC ADMIN.xlsx workbook? if not, what about this one?

You want 2 buttons:
1) for a), b) and c)
2) for e)

Confirm or rectify this, please adjust workbooks and worksheet names too.

Regards!
 
Hello - hope you have had a good day?

A) yes correct

B) the workbook we receive daily we just copy the one tab from it onto our MAC TRACKER workbook. Ignore everything else on the other tabs of the workbook.

C) I want to take new data only as old data would have already been copied over. I don't know if the file can tell from when the last row was updated. So Monday we have data rows 1 through to 15, Tuesday we have data on rows 1 through to 20, so the file should recognise that rows 16-20 are new and so we should copy those rows only onto the relevant tabs using the project type A to determine what tab it should go on. If it can store the last JLL MAC NO in column E, then we can use that.

D) Yes they do but only from columns P-AF as the previous columns should have been copied already from JLL MAC ID's.
This file is saved in a shared directory for access to the team, only one person can complete one job as they will have their name assigned in column G.

E) Once a week the admin person will want to copy the data from MAC TRACKER to MAC ADMIN file to the all, completed and scheduled file as you say.

2 buttons, 1 for C and 1 for E

Added the 2 workbooks, MAC ADMIN & MAC TRACKER
 

Attachments

  • MAC ADMIN.xlsx
    32.3 KB · Views: 5
  • MAC TRACKER.xlsm
    138.4 KB · Views: 3
Hi, emmatm!

I've had an excellent day, my boss is travelling and I think he has internet issues, so a calm journey. Hope you too.

About e).

The process we've arranged previously operates from the source worksheets of main workbook (Projects, Tech & co, ...) to the target worksheets of the other workbook -Admin- (All, Scheduled, Completed). But in this new project, with new six-packs of Carlsberg, of course!, these last worksheets are present both in Tracker and Admin workbooks... so that's my question against which set of ws should it work.

Regards!
 
Hi, emmatm!
Forget about e), I just discovered that you were playing on me, the new uploaded tracker file doesn't contains any more those f_____g worksheets.
Regards!
PS: Replace underscores with an arrangement of these letters: a i l l n
 
So E) should be as follows:
MAC TRACKER tabs 'projects', 'tech & crate', 'tech only', 'crate only' & 'OH' will be moved to tabs 'all', 'scheduled' & 'complete' on the MAC ADMIN file using the status column 'P'
 
Hi, emmatm!
I'm having doubts about which meaning should I read.
Regards!

breakdown translations

sustantivo

el
desglose
breakdown, apportionment

la
descomposición
decomposition, breakdown, abashment

la
ruptura
rupture, breaking, breakdown, breakup, disruption, breach

la
avería
fault, breakdown, failure, damage, trouble

el
fracaso
failure, flop, breakdown, fiasco, bust, miss

el
análisis
analysis, test, review, assay, breakdown

el
desajuste
maladjustment, breakdown, disarrangement

la
depresión nerviosa
breakdown
 
Hi SirJB7,

Can you advise if you have any new suggested coding for my last workbooks sent to you?
I cannot see that you had sent me anything back.

I am conscious that you are a very busy man so I am not chasing just want to be sure I haven't missed anything!

Thanks as always
 
Hi, emmatm!

Emm... atm... what? workbooks... o_O... Oh!... those workbooks...:oops:
Do you believe me if I tell you that I have absolutely forgotten about them?
I'll take a bit more time to get back to you with something about them.
emmatm said:
I am conscious that you are a very busy man so I am not chasing just want to be sure I haven't missed anything!
Click to expand...

What a pity! How much I like to be chased... well, another time will be.
The history of my life, everybody chases me but no one grabs me...

Regards!
 
Hey don't worry, I have been taking a look online and trying a few things and I have done some of this myself.:DD
Leave it for now and if I get stuck I will give you a chase for some help.

Hope you have had a good day?
 
Hi, emmatm!
Give a look at the uploaded files, they're not fully tested but that's your homework. Just advise if any issue.
Regards!
 

Attachments

  • MAC ADMIN.xlsx
    32.6 KB · Views: 5
  • MAC TRACKER.xlsm
    151 KB · Views: 7
You must log in or register to reply here.
Back
Top