After Excel Insert columns , formula can't display inserted data
Hi Expert,
Attached file , Sheet 1 with formula reference to sheet 2 .
After insert columns at sheet 2 , sheet 1 formula can't display new added datas .
Can excel auto update the formula ?
Thanks
Stanley
Sheet-b-insert.xls
Attached file , Sheet 1 with formula reference to sheet 2 .
After insert columns at sheet 2 , sheet 1 formula can't display new added datas .
Can excel auto update the formula ?
Thanks
Stanley
Sheet-b-insert.xls
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Gerwin Jansen
Depending on what you want, you can change formula on sheet1 A15 to: =Sheet2!$A$16
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I think there is something wrong with your version of excel.
i am using Office 2010 prof 64bit. your file is working fine in there.
I inserted column and added data, everything reflecting in sheet1
automatically.
tell me what version your are using? office 2003?
just repair your office. that's better.
i am using Office 2010 prof 64bit. your file is working fine in there.
I inserted column and added data, everything reflecting in sheet1
automatically.
tell me what version your are using? office 2003?
just repair your office. that's better.
Excel is working fine!! You are inserting rows.
When you do the insert rows on sheet2 the formula on sheet1 recognises that the cells have moved and change the formula accordingly because the formula is a direct link to the cell.
If you need the link to stay absolute rather than dynamic, use the INDIRECT suggestion by nutsch.
Or after insertion, go back to sheet1 and copy the formula from A1 down to the right number of cells. Quick way is to double click the bottom right hand corner and this will fill down to existing area. New formula might be needed on the bottom to accommodate the new entries on sheet2.
Cheers
Rob H
When you do the insert rows on sheet2 the formula on sheet1 recognises that the cells have moved and change the formula accordingly because the formula is a direct link to the cell.
If you need the link to stay absolute rather than dynamic, use the INDIRECT suggestion by nutsch.
Or after insertion, go back to sheet1 and copy the formula from A1 down to the right number of cells. Quick way is to double click the bottom right hand corner and this will fill down to existing area. New formula might be needed on the bottom to accommodate the new entries on sheet2.
Cheers
Rob H
ASKER
Silly me ignore INDIRECT function .
Thanks
Thanks
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
