I need a fix for an excel formula recalculation error.
September 21, 2006 8:50 AM Subscribe
I can't get a formula in excel to recalculate when copied and pasted to another cell. Help!
I'm trying to get three of the same bits of data from 300 different worksheets in an excel file. Each worksheet is named with a person's first and last name.
I inserted a new worksheet, typed in each person's first and last name into separate cells in column A.
Then I am using an indirect formula to grab the data from each worksheet.
The formula I'm using is
= INDIRECT ("'"&$A2&"'"&"!"&B$1)
Example Worksheet:
A B C D
1 C62 h62 m61
2 Jane Doe
3 Bob Lugnut
4 John Smith
The Formula works fine. The problem I have is when I cut the formula from row 2 (Jane Doe's data) to row 3 (Bob Lugnut's Data), Jane Doe's data remains until I go into the cell and hit return. Only then does the cell recalculate the formula and show Bob's data.
Because the entire point of this exercise is so I don't have to go in and manipulate EVERY single cell to see the data, I know there MUST be a way to fix this.
If it helps, I've used this formula before with a different excel file and it works and recalculates fine.
I also tried starting over in a new worksheet, thinking maybe something about that worksheet was effed up, but it didn't help.
Help me Hive Mind!
I'm trying to get three of the same bits of data from 300 different worksheets in an excel file. Each worksheet is named with a person's first and last name.
I inserted a new worksheet, typed in each person's first and last name into separate cells in column A.
Then I am using an indirect formula to grab the data from each worksheet.
The formula I'm using is
= INDIRECT ("'"&$A2&"'"&"!"&B$1)
Example Worksheet:
A B C D
1 C62 h62 m61
2 Jane Doe
3 Bob Lugnut
4 John Smith
The Formula works fine. The problem I have is when I cut the formula from row 2 (Jane Doe's data) to row 3 (Bob Lugnut's Data), Jane Doe's data remains until I go into the cell and hit return. Only then does the cell recalculate the formula and show Bob's data.
Because the entire point of this exercise is so I don't have to go in and manipulate EVERY single cell to see the data, I know there MUST be a way to fix this.
If it helps, I've used this formula before with a different excel file and it works and recalculates fine.
I also tried starting over in a new worksheet, thinking maybe something about that worksheet was effed up, but it didn't help.
Help me Hive Mind!
Best answer: Is there some reason you can't just hit F9 to manually recalculate the worksheet? (CTRL+ALT+F9 recalculates the entire workbook if I'm not mistaken).
posted by zixyer at 9:06 AM on September 21, 2006 [1 favorite]
posted by zixyer at 9:06 AM on September 21, 2006 [1 favorite]
Response by poster: Zixyer:
You are a genious and a scholar. That worked. Thanks. :)
posted by batcrazy at 9:08 AM on September 21, 2006
You are a genious and a scholar. That worked. Thanks. :)
posted by batcrazy at 9:08 AM on September 21, 2006
Best answer: Also, under the Tools menu, select Options, then select the Calculation tab in the popup. Make sure "Automatic" is checked.
posted by allkindsoftime at 9:10 AM on September 21, 2006
posted by allkindsoftime at 9:10 AM on September 21, 2006
Woah I've been having this problem too! I thought I'd have to reinstall Excel.
I downloaded my budget from Google Spreadsheets and nothing works unless you click the cell. Even so some formulas that worked fine in Google, that were from an Excel template, say "VALUE!" now. Then I opened my old original file and it doesn't work either. The only things that work are the Excel templates. I have to do everything in Google now.
I'll look at that "Automatic" thing, too.
posted by scazza at 10:45 AM on September 21, 2006
I downloaded my budget from Google Spreadsheets and nothing works unless you click the cell. Even so some formulas that worked fine in Google, that were from an Excel template, say "VALUE!" now. Then I opened my old original file and it doesn't work either. The only things that work are the Excel templates. I have to do everything in Google now.
I'll look at that "Automatic" thing, too.
posted by scazza at 10:45 AM on September 21, 2006
Response by poster: Allkindsoftime:
That was the underlying problem--thanks!
posted by batcrazy at 2:29 PM on September 21, 2006
That was the underlying problem--thanks!
posted by batcrazy at 2:29 PM on September 21, 2006
This thread is closed to new comments.
posted by batcrazy at 9:06 AM on September 21, 2006