Lock formulas within Table

[Domroy]

Hey all,

I have a workbook in which I move records from one tab (table) to another regularly. The tables are identical, as I have to just move the records while the individual goes through a process, and track it. Each time I move the record to the new table, the field in which I calculate the age (based on birth date) brings in the previous table reference (subsequently screwing up all the records in the new table). I have to go in and edit it every single time. Is there a way to lock the formula so that it just references the DOB field each time, and not the previous table? Here's my formula:

=IF(ISBLANK([@DOB]),"",INT((TODAY()-[@DOB])/365))

Every time I move it, it adds the table reference before [@DOB].

Thanks so much! This will save me tons of time if we can fix.

Judi

 

[padapinto]

Hey all,

I have a workbook in which I move records from one tab (table) to another regularly. The tables are identical, as I have to just move the records while the individual goes through a process, and track it. Each time I move the record to the new table, the field in which I calculate the age (based on birth date) brings in the previous table reference (subsequently screwing up all the records in the new table). I have to go in and edit it every single time. Is there a way to lock the formula so that it just references the DOB field each time, and not the previous table? Here's my formula:

=IF(ISBLANK([@DOB]),"",INT((TODAY()-[@DOB])/365))

Every time I move it, it adds the table reference before [@DOB].

Thanks so much! This will save me tons of time if we can fix.

Judi

incase you have put dollar sign in your formula, you need to remove them thus unlocking the cell so when you move your data it moves all of it just like the rest since you said that its only cell with date of birth that doesn't move.

 

[Domroy]

I didn't put the "$" because it didn't seem to work. That's normally how I would do it, but using the column name as the reference, it didn't like it.

 

[padapinto]

I didn't put the "$" because it didn't seem to work. That's normally how I would do it, but using the column name as the reference, it didn't like it.

i asked so because when a cell is locked then you move its contents to another sheet, it will still reference to the same cell. which cell is D.O.B. WRITTEN, you could use a formula say like if a certain cell has data, then formula shud take the contents of cell with date of birth in the new table. so in both tabs, both cells that should have DOB always have the same data. you can use the cell that carries the name of the first person. =IF(SHEET1!A2="",A1,"") SO WHENEVER YOU MOVE THE DATA, THEN MAIN TABLE IS LEFT EMPTY AND FORMULA WILL TAKE DATE OF BIRTH THATS IN A1 OF THIS SHEET YOU MOVED DATA TO. BOTH A1 IN THE 2 TABLES WILL HAVE THE SAME DATA FOR YOUR FORMULA TO WORK

 

[Domroy]

Hmm...I'm not sure I understood what you said, but it got me to thinking that maybe if I lock it and use the column letter name ("M") rather than the named range ("DOB"), that might work. I'll check it out when I get back to the office. I still think it'll try to take the table name with the formula when moved, but I'll try.

 

[padapinto]

Hmm...I'm not sure I understood what you said, but it got me to thinking that maybe if I lock it and use the column letter name ("M") rather than the named range ("DOB"), that might work. I'll check it out when I get back to the office. I still think it'll try to take the table name with the formula when moved, but I'll try.

ALL you need is to give the cell a 2nd choice when moved it can use the second choice. using if function

 

[Peter_SSs]

use the column letter name ("M") rather than the named range ("DOB"), that might work.

Given that your table structures are identical that is what I would do.

=IF(ISBLANK(M2),"",INT((TODAY()-M2)/365))

 

[Domroy]

Yup. Using the cell reference rather than the named range did it. Thanks for bouncing the ideas around.

 

[Peter_SSs]

Glad you got it sorted - yourself in the end.