Copying a formula using several tabs and different cells on a summary sheet I ha
ID: 3563604 • Letter: C
Question
Copying a formula using several tabs and different cells on a summary sheet
I have this worksheet with several taps. Each tap represent the date in a month. Each tab contain subtotal. I use this subtotal and add them in a summary page. I want to be able to copy the formula in the summary page but is changing the date and the cell number. What can I do to just simply copy and drag the formua with out changing the tab number and the cells. Can you help me?
Sept 26 is the tab and I am adding Cells C13, C35 and C58 from each tab in a summary page.
='Sept 26'!C13+'Sept 26'!C35+'Sept 26'!C58 (day 1)
='Sept 27'!C13+'Sept 27'!C35+'Sept 27'!C58 (day 2)
=Sept 28'!C13+'Sept 28'!C35+'Sept 28'!C58 (day 3)
Day 2014 Gross Mobility Day 1 $9,375.00 (this is the information from Sep 26) Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 Day 8Explanation / Answer
Put following formula and drag down -
=INDIRECT("'"&TEXT("09/25/14"+ROWS($1:1),"mmm dd")&"'!"&"$C$13")+INDIRECT("'"&TEXT("09/25/14"+ROWS($1:1),"mmm dd")&"'!"&"$C$35")+INDIRECT("'"&TEXT("09/25/14"+ROWS($1:1),"mmm dd")&"'!"&"$C$58")
It assumes that after Sep 30, next tab would be Oct 01.
If single digit numbers are not 01, 02 but 1, 2...(Hence tab would be Oct 1 not Oct 01)...Then use following formula and drag down -
=INDIRECT("'"&TEXT("09/25/14"+ROWS($1:1),"mmm d")&"'!"&"$C$13")+INDIRECT("'"&TEXT("09/25/14"+ROWS($1:1),"mmm d")&"'!"&"$C$35")+INDIRECT("'"&TEXT("09/25/14"+ROWS($1:1),"mmm d")&"'!"&"$C$58")