Dashboard

**pepps** · 06-05-2012, 07:52 PM

I have a row with 12 cells lets say B1:B12 (they stand for Jan-Dec). Each cell could be blank or have an "G", "A", or "R". These 12 cells track the monthly project status for a year. There is another cell, A1 that will always have an "G", "A", or "R". A1 represents the current months Project status. There is a "Status Reporting Date" cell, A2, that is filled in with the current date.

What I want to do is evaluate the "Status Reporting Date", using something like MONTH(A2), when the reporting date is equal to the month in cells B1:B12, then update that months cell in B1:B12 with the "G", "A" or "R" from the A1 Current Month Project Status. However, when the Status Reporting Date does not match the other 11 months (cells), I don't want to over-write the content of the B1:B12 cell. (this is the part I can't get to work)

The cells might look like this, then each month when the report is updated, the matching cell would be updated with the new status.

The first two cells are blank because the project didn't start until March. The other 10 cells hold the monthly history of the project.
| | |A|A|G|G|G|G|A|R|A|G|

Any ideas how to update the 12 cells, without changing the value of each cell, unless the Status Reporting Date matches the cell representing that month?

Thanks

**thepdaoson** · 06-05-2012, 11:31 PM

It is difficult to understand your expectation. Could you please upload your file and give an example of the result you want. If I understand you, it may be like: When you type 3 (for March) in cell A2 then in the A1 the status of project in March (A) will be given automatically? If so, it could be done in a minutes.

**abousetta** · 06-05-2012, 11:55 PM

Hi @pepps and welcome to the forum,

If you open the workbook at least once a month then you can use a simple macro to do this.

Put this in "ThisWorkbook" not a standard module

Please Login or Register  to view this content.

I am assuming that the first sheet is the dashboard. If not then change Sheets(1) to the correct number or put the name of the sheet in quotation marks instead of the 1 (e.g. Sheets("My Dashboard").

Hope this helps.

Good luck.

abousetta

**pepps** · 06-06-2012, 12:12 AM

I think that will work, I'll try it. Thank you.

How do you attach a sample Excel file to a message?

**abousetta** · 06-06-2012, 12:24 AM

Click on the Go Advanced then either the paperclick image or scroll down to where it says Manage Attachments.

**pepps** · 06-06-2012, 09:24 AM

Attached is a sample of what I'm trying to do.

**abousetta** · 06-06-2012, 09:33 AM

Here's a modified version of the code to work with the design you uploaded:

Please Login or Register  to view this content.

**pepps** · 06-06-2012, 02:01 PM

It's been a long time since I coded, so I have a few simple questions.
1. What does "Month" in the code myMonth = Month (.Range ("E1")) return if the value of "E1" is 11/5/2012?
2. In the example spreadsheet, I assume "c" is the index that is initilized to 1 and automatically stops after 12 iterrations?
3. What is the value of c.Column -1 on the first loop and how would that = Month? (unless Month is a integer between 1 and 12?

Don't mean to be so dense...

**abousetta** · 06-06-2012, 02:14 PM

Originally Posted by pepps

What does "Month" in the code myMonth = Month (.Range ("E1")) return if the value of "E1" is 11/5/2012?

Month is the month of year in integers (1-12). You will have to check your computer's settings, because 11/5 could be May or November depending on the setting.

Originally Posted by pepps

2. In the example spreadsheet, I assume "c" is the index that is initilized to 1 and automatically stops after 12 iterrations?

C is a range (e.g. c as in cell, but you can change it any meaningful name). So when I say each c in myRng, I am saying each cell in my range. If I am not mistaken, c is an object in itself (but don't quote me on that, but I do know it's a range).

Originally Posted by pepps

3. What is the value of c.Column -1 on the first loop and how would that = Month? (unless Month is a integer between 1 and 12?

You started the months in column 2 (B) instead of column 1 (A), so I had to adjust accordingly. c.column would return the column number in which c resides. Therefore 2 - 13, but I want 1 - 12 so I subtract one from the column number.

Hope this helps.

abousetta

**pepps** · 06-06-2012, 02:33 PM

Ok, pretty close to working. It is looping correctly and indexing great. The only issue I have left is that the field I'm coping contains a formula that returns a "G", "A", or "R" status. Instead of the status being copied, it is coping the formula. So as you update the project status, all 12 of the monthly status execute the formula and are identical. Is there a way to copy what shows in the cell "G", "A", "R", instead of the formula?

**abousetta** · 06-06-2012, 04:19 PM

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

this will bring the values only

**pepps** · 06-06-2012, 04:54 PM

Thank you very much!!! Works great

**abousetta** · 06-06-2012, 05:09 PM

Thanks for the feedback.

Good luck.

abousetta

Dashboard

LinkBack

Thread Tools

Rate This Thread

Display

Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Re: Dashboard

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions