• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Permanently link formula references to Column Names and not Column Cell Numbers

E

Ecel Dumbo

Member
Hello,


Please find attached file

http://speedy.sh/trYnv/Book1.xls


I have a formula in which the references are cell numbers. I would like to permanently link the references to column names, so when i add/delete columns the references in the formula will not change.


thanks

Excel Dumbo
 
Excel Dumbo


I'm confused


If you delete column F the formulas automatically adjust themselves

If you add a column next to F the formulas automatically adjust themselves


Can you explain what is happening to require a more complex formula?
 
hi hui, the table is actually a pivot table. I noticed when i change the pivot table structure, the external calculations go haywire. IF the formula was looking at cell#C2 which has sales dollars, when i restructure the pivot to include additional column, the formula will look at still look at #C2 but this tinme with sales units. I want the formula to permanently look at sales dollars
 
=IF(INDEX(C6:G6,,MATCH("2012 Actuals Dec'11 to Nov'12",C5:G5,0))=0,"",(INDEX(C6:G6,,MATCH("Revised Quota",C5:G5,0))-INDEX(C6:G6,,MATCH("2012 Actuals Dec'11 to Nov'12",C5:G5,0)))/INDEX(C6:G6,,MATCH("2012 Actuals Dec'11 to Nov'12",C5:G5,0)))


You may have to Change the Column G to suit


ie:

=IF(INDEX(C6:Z6,,MATCH("2012 Actuals Dec'11 to Nov'12",C5:Z5,0))=0,"",(INDEX(C6:Z6,,MATCH("Revised Quota",C5:Z5,0))-INDEX(C6:Z6,,MATCH("2012 Actuals Dec'11 to Nov'12",C5:Z5,0)))/INDEX(C6:Z6,,MATCH("2012 Actuals Dec'11 to Nov'12",C5:Z5,0)))
 
HI Hui, Thanks for your input. I tried the above formuala. It is giving me #NA


please see link below


http://speedy.sh/3xssQ/permanent-link-to-column-name-in-formulav2.xls


Regards
 
Here is your file with it working

https://www.dropbox.com/s/ovylpwuivucbr0e/Ecel%201302.xls


Note that the ranges have been shortened back to Column G so they don't overlap the results area


Your new file also has a #N/A error in G6 which isn't good
 
You must log in or register to reply here.
Back
Top