Macro or not???

5 pts.
Tags:
Macros
I have a spreadsheet for a team of agents stats, the front page has agents along the top and stats down the left hand side. Each subsequent tab has that agents stats down the side and each column is the progressing weeks and a tab for the team which collates the agents info in to team figures...at the moment the front page displays the info for the first week for each tab...I want the info displayed on the front page to change the information it displays as the weeks progress, so in week 1 it'll display each tab column B, then at the start of Week 2 it'll change to display each tab column C and so forth...How do I do this??? Would it be a macro and if so how would I write it??? Or would it be linked to the current date/time function with a formula???

Software/Hardware used:
Microsoft Excel 2003
ASKED: January 7, 2012  11:15 AM
UPDATED: February 28, 2012  12:14 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chippy088
    Not that hard to do. I have done a similar work book with a sheet for sales people at a shop, weekly sales totals for that shop, consolidated under an area manager. The way I'd do it, if I understand it right, is as follows. sheet 1 is the consolidation sheet.. sheet 2 is the sheet for agent 1 sheet 3 is the sheet for agent 2 etc Assuming the cell you want to copy is on the agent(x)_tab in column 1 (we'll call it a:20 for want of a definitive cell) On consolidation sheet in cell B2, (against agent 1) enter this =IF(Sheet2!A20=""," ",) On consolidation sheet in cell C2, (against agent 1) enter this =IF(Sheet2!B20=""," ",) This copies the content of cell a20 of tab 2 ( stats for agent 1) . If the sheet hasn't been used it will be empty, and the if formula shows the content if not empty cell, else it leaves it blank. If you don't use the if formula, then a 0 will be copied into the cell. It will look better if the cell is empty when you print it out. Do the same for Sheet2!B20 to the last cell you want on the consolidation sheet. Then do the same foreach agent on their own line. The consolidation sheet will look something like this. Agent 1 agent 1 week 1 agent 1 week 2 agent 1 week 3 agent 1 week 4 agent 1 week 5 Agent 2 agent 2 week 1 agent 2 week 2 agent 2 week 3 agent 2 week 4 agent 2 week 5 Agent 3 agent 3 week 1 etc Agent 4 agent 4 week 1 etc I decided to do it this way because it was easier than a macro, and did the job.
    4,625 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following