Excel Sheet Setup (Advance)

han77729

New member
Joined
Dec 12, 2012
Messages
31
Reaction score
0
Points
0
Hello All,

Going to go straight to the point.

Questions one: I'm having trouble to linking values in cells to another sheets.. I know it is as easy as having "=.. and whatever the cell you want to link to" But the problem I'm having is that I'm trying to make the linking thing not only links the value, but the color set inside that cell. Could anyone help?

Questions two: As I'm working on linking the cells together from multiple sheets (sheet 1, sheet 2.. etc) into one sheet (collaboration sheet), I came across another issue. I'm trying to let sheet "1"'s value always stays on the top of the "collaboration sheet" and sheet "2"'s value always go underneath "sheet 1"'s values and sheet "3"'s value stay under sheet "2"'s value. (easy right?) BUT, in addition.. when I "add" more values into the original sheet 1,2 and 3.. it needs to stay in order on the collaboration sheet.

Could anyone please help me fix this problem?

I've attached a setup workbook so you guys can mess around with the formula/functions.

Thank you,

HanView attachment Excel Help.xlsxView attachment Excel Help.xlsx
 
Hi Han. I suggest you look at http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables where I helped someone pull together data from separate tables on different sheets into one. I'll need to make some minor modification so that it also brings through color...at the moment it only does values if I remember correctly. But this is very minor.

THe order thing is not a problem...my approach will let you use any order you want.

I'll post something when I get a moment.
 
Jeff,

You're a lifesaver. This is great, please do assist me on getting the colors through. I'm very curious how you make those "tab buttons" with colors.. it makes the sheet so much better looking. Please send me some guided tutorials or a direction to where to look for.

I appreciate all your support.

Han

Hi Han. I suggest you look at http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables where I helped someone pull together data from separate tables on different sheets into one. I'll need to make some minor modification so that it also brings through color...at the moment it only does values if I remember correctly. But this is very minor.

THe order thing is not a problem...my approach will let you use any order you want.

I'll post something when I get a moment.
 
Jeff,

I just want to follow up with you on this. Have you find a solution for the color cells? Is there anything I could provide you to help you?

Thanks,

Hi Han. I suggest you look at http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables where I helped someone pull together data from separate tables on different sheets into one. I'll need to make some minor modification so that it also brings through color...at the moment it only does values if I remember correctly. But this is very minor.

THe order thing is not a problem...my approach will let you use any order you want.

I'll post something when I get a moment.
 
Updated Spreadsheet for Revisment

Apologies about the repeated messaged. But I want to let you know I've adjusted the spreadsheet and add in new columns and data rows for the stuff I'd like to show up on the Summary page. Your assistace would be very much appreciated.

Thank you JeffView attachment Revised Table.xlsb



Jeff,

I just want to follow up with you on this. Have you find a solution for the color cells? Is there anything I could provide you to help you?

Thanks,
 
Sorry han77729, I got sidetracked. Quick question: is there any rules regarding what gets highlighted?
 
Not a problem Jeff. Yes there are. If you take a look at the AMERICA Sheet, in column J,K and L there are two cells per regular cell comparing to all the other columns and we did that because we wanted a forecast date and a actual date.

The "forecast date" is marked with the color Grey and the "Actual date" is marked with the colors below
Green = equal or before forecast date
Red= Past forecast date
White = Blank

This color scheme should apply to all sheets for columns J, K and L

Thanks Jeff,

Han
Sorry han77729, I got sidetracked. Quick question: is there any rules regarding what gets highlighted?
 
Okay. You'd actually be best to use conditional formatting on the Summary sheet to do this, so that it happens automatically. I'll whip something up that is a little more advanced than the current conditions.

Also, I see you've put the words "Priority", Description, and Status in merged cells. Word to the wise...never ever merge cells. It causes VBA to fail. Instead, use the 'Center across selection' alignment option from the Format Cells dialog box that comes up when you push F1.

SHould have something for you soon.
 
Thinking about this some more, I think you'd be better to handle 'Forecast' vs 'Actual' by introducing another column 'Actual' into the table rather than reusing rows. THis sound okay to you?
 
I guess the con to that is having additional columns, wouldn't that look too repetitive and excessive? But let's give it a go and see what we have. Great lesson on the merge cells. Thank you

Appreciate your support,

Ham
Thinking about this some more, I think you'd be better to handle 'Forecast' vs 'Actual' by introducing another column 'Actual' into the table rather than reusing rows. THis sound okay to you?
 
valid point. Your call. There would only be there additional columns, and maybe users would only fill them in if dates differed from forecast? The beauty of having it in seperate columns is that it makes it easier to filter on say the color red and/or pivot the data so you can only look at things that are say past forecast date, while still being able to see what the original date actually was.

Whereas if they are on seperate rows, as soon as you filter on 'past forecast date' then you filter out what the actual forecast date was in the first place. Might not be an issue to you.

Thoughts?
 
Makes sense. This will become useful when filtering and mining for specific data. Let's proceed with the additional columns and make them separate.

Thank you,

Han
valid point. Your call. There would only be there additional columns, and maybe users would only fill them in if dates differed from forecast? The beauty of having it in seperate columns is that it makes it easier to filter on say the color red and/or pivot the data so you can only look at things that are say past forecast date, while still being able to see what the original date actually was.

Whereas if they are on seperate rows, as soon as you filter on 'past forecast date' then you filter out what the actual forecast date was in the first place. Might not be an issue to you.

Thoughts?
 
You could always put the extra 'Actuals' columns side by side, and group them so that a user can expand/contract them if they are/are not relevant.
 
Is it only the dates that will change, or financial figure too? i.e. you just need to track date slippage, not financial slippage?
 
Once the entry has been entered (entire row) both financial and dates can change.

Is it only the dates that will change, or financial figure too? i.e. you just need to track date slippage, not financial slippage?
 
Sorry Han, this slipped off my radar. Thanks for your prompt. Will take a look today.
 
Hi again, Han. I take it that when the data is amalgamated, you still want to be able to see what tab each bit of data was pulled from? Also, to match up the forecast and actuals for each item, you really need a unique ID that they both share.

I'll put something together that addresses both of these, and also fix the tabs so that they are the same size and don't move around when you make changes to the worksheet.
 
Also, will every row ultimately have a 'forecast' and 'actual' component? Or do you just put in the ones that have slippage?
 
Back
Top