Please Help: Percentage changes from 2 sheet showed on the same cell

[Khanh_Mai]

Hello,

I have 2 data sheets and I am trying to show the percentage changes resulted from these sheets and show the value in the same size in either sheets. 

The result can look like the value of B3 in "Week 1" sheet with -10% is percentage changes when the data from the "Week 2" sheet minus the ones from the "Week 1". Please find the example Excel file as attached.

Can anyone help please? Thank you. 

Khanh Mai

[#2 · Accepted solution]

@Khanh_Mai  short answer is you would need some VBA code to do it.  But that said there are some tricks you can do to get something very similar.  Follow these steps:

1.  Select BOTH sheets (this will make these actions happen on BOTH sheets at the same time)
2.  Add an extra row after each,
3.  add the calculated difference in the new row.
4.  format new row (make text smaller? bold? make text green for + and (red) for -, etc...)  here is the custom format I used in the attached:     [Green]0.00%;[Red](0.00%);"n/c";@  
5.  Select 1st pair of rows and open the Borders menu -> more borders and click on horizontal line to remove line between the rows.
6.  (1st 2 rows still selected) Fill white 
7.  (1st 2 rows still selected) Select format painter
8. Select the rest of the table (i.e. paint the new formatting on the rest of the table)

[#3]

Hello @mtarler,

Thank you so much for your help.

It is simple and works great as I desire. The selection of 2 sheets and the actions happened on both of them is very useful for me.

Khanh Mai

[#4]

Hello @mtarler,

I am trying to use your answer applied to more complex data range that means my workbook contains more data rows that need to insert the blank rows (about 500 data rows) (for percent changes stayed in) between each data row. I am currently adding the blank rows manually but it seems hard to do that for 500 data existing rows.

Can you please help me how to insert the blank rows between each existing row easier? 

Thank you.

Khanh Mai

[#5]

@Khanh_MaiYou could write a quick macro to do it.  Something like:

for i = 500 to 1 step -1

  activesheet.cells(i,1).entirerow.insert

next i

You could also use a new sheet and create a formula on 1 row to ref the other sheet and the 2nd row to be the new % and then fill down.

The formula on the 1st row could use the new FILTER or traditional formula like offset('Sheet1'!$A$1,(row()+1)/2,0)

[#6]

Hello,
Thank you for your suggestion. I found the solution to this.
Khanh