Forum Discussion
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 client name.
The subsequent sheets are monthly income sheets where there are formulas and such that determine the amount of income per client. I have to manually find the cell where I want the information pulled into the first sheet, but the cell is the same on every monthly income sheet.
so my table has the following columns and formulas added to it:
Client name Jan Feb Mar etc. etc.
Joe's coffee =sheet1!AB1 =sheet2! =sheet3! etc. etc.
How do I copy the formula under January into the rest of the row so that I can keep the sheet reference and the cell reference (AB1 in this example) remains the same? =sheet2!AB1 =sheet3!AB1
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
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.
