CaptCDelany
New Member
- Joined
- May 2, 2014
- Messages
- 4
Hey guys,
I tried searching the forum and couldn't find anything that would work so I figured I would ask. I am working on a new quasi dashboard type project at work. I am trying to create a dashboard for some job costing sheets we use. To give you the set up:
I would like to be able to first transfer the data from each months sheet into another "master" worksheet which would list the data for all past sheets and have it formatted with no empty rows or columns. I figured I could have the specific areas listed in "data boxes" in the rows and the progression of dates across the columns. This, in my mind would allow me to simplify a dashboard.
I have tried a couple different ways of accomplishing this (such as indirect lookup etc). Every way I have tried required me to go in and change the formula for the sheet reference for every formula since each sheet would be titled with a different date.
I guess what I may be looking for is a Macro that would say: Copy this data from these cells and paste it on this (master) worksheet. The Macro would need to pull the data from the last worksheet listed as they go in chronological order and paste it into the "next empty column" on the master sheet. Then again, I have no idea what I need/how to go about it, that is why I am here.
Thank you in advance for this answer and all the other answers I have found on here. I will be able to get screenshots tomorrow to help clarify what I am talking about if need be.
Chris
I tried searching the forum and couldn't find anything that would work so I figured I would ask. I am working on a new quasi dashboard type project at work. I am trying to create a dashboard for some job costing sheets we use. To give you the set up:
- We have a template that we use for each job. (construction) It documents labor cost & time as well as material cost in one column and breaks it down "by unit" in the next column.
- Within this template the data is not in a continuous table. Meaning there are blank rows and columns that separate boxes that contain the data I am after. (set up this way for printing) (imagine a box with two columns and 5 rows, then a few empty rows down is another box)
- Each month a new worksheet following that same template is added to the workbook with the values updated. This is based on what was completed and billed for the prior month.
- These new worksheets are named by a date and initials.
I would like to be able to first transfer the data from each months sheet into another "master" worksheet which would list the data for all past sheets and have it formatted with no empty rows or columns. I figured I could have the specific areas listed in "data boxes" in the rows and the progression of dates across the columns. This, in my mind would allow me to simplify a dashboard.
I have tried a couple different ways of accomplishing this (such as indirect lookup etc). Every way I have tried required me to go in and change the formula for the sheet reference for every formula since each sheet would be titled with a different date.
I guess what I may be looking for is a Macro that would say: Copy this data from these cells and paste it on this (master) worksheet. The Macro would need to pull the data from the last worksheet listed as they go in chronological order and paste it into the "next empty column" on the master sheet. Then again, I have no idea what I need/how to go about it, that is why I am here.
Thank you in advance for this answer and all the other answers I have found on here. I will be able to get screenshots tomorrow to help clarify what I am talking about if need be.
Chris
Last edited:
