Forum Discussion

Rachel Westmoreland's avatar
Rachel Westmoreland
Copper Contributor
May 01, 2018

copy and paste a formula that does not change the cell location

I hope I can explain this correctly.  I am very new to excel terminology even though I have used it most of my life. I have a workbook where the first sheet is a main table where each row is a cli...
Macros & VBA
Need Help
Tomasz Kocur's avatar
Tomasz Kocur
Brass Contributor
May 03, 2018

There are several ways to do that (please see attached file)

My suggestion is to change worksheets name to months name: 

sheet1 change to Jan

sheet2 change to Feb etc

 

use INDIRECT formula combined with "dollar sign" cells address  (see first 4 clients)

=INDIRECT(B$1&"!B2")

 

but... instead of manually finding the cells where you want the information pulled from use SUMIF function combined with INDIRECT (see customers 5 - 19)  

=SUMIF(INDIRECT(B$1&"!$A:$A"),$A7,INDIRECT(B$1&"!$B:$B"))

 

then regardless of what order you have entered customers, it will calculate the sum of the income

 

  • Rachel Westmoreland's avatar
    Rachel Westmoreland
    Copper Contributor
    May 04, 2018

    Thank you very much!! The INDIRECT formula worked beautifully!

    I'm not sure how the second one works exactly, it is over my head a bit.  But the spreadsheets I'm pulling data from are not exactly straightforward.  There are several factors involved which is why I need to find it manually.  Hopefully it will help someone else though.  I truly appreciate your help and creating this spreadsheet to explain it.

Resources