Home |
Search |
Today's Posts |
#1
|
|||
|
|||
LOOKUP: change referenced spreadsheet
With the upgrade in Excel, I can no longer use 'Find and Replace All' to
change a spreadsheet referenced in my VLOOKUP statements (everything is in one file). The 'old' and 'new' spreadsheets are identical except for the data and number of lines, columns and headings are the same. Now, two windows appear to select the file and worksheet for each occurance of the formula - which happens hundreds of times. Previously, I would highlight the column containing the VLOOKUP formula, select 'Find', enter the old sheet name and new sheet name, and select 'Replace All'. |
#2
|
|||
|
|||
LOOKUP: change referenced spreadsheet
I think if you look closely, you misspelled the name of the new worksheet.
Just a tip...when you do this, select a small range first. Then if you make a mistake, you'll only have to dismiss a few dialogs. Barb wrote: With the upgrade in Excel, I can no longer use 'Find and Replace All' to change a spreadsheet referenced in my VLOOKUP statements (everything is in one file). The 'old' and 'new' spreadsheets are identical except for the data and number of lines, columns and headings are the same. Now, two windows appear to select the file and worksheet for each occurance of the formula - which happens hundreds of times. Previously, I would highlight the column containing the VLOOKUP formula, select 'Find', enter the old sheet name and new sheet name, and select 'Replace All'. -- Dave Peterson |
#3
|
|||
|
|||
LOOKUP: change referenced spreadsheet
Thanks for the suggestion, but the names are spelled correctly. I am merely
changing an FY05 to an FY06 - the rest of the name remains the same. I do test a small sample of six cells, four of which contain the formula in question. (The other two sum the rows.) The 'Find and Replace All' worked fine before updating to Excel 2003. Any other suggestions would be grealy appreciated, as I have relied on this form of updating spreadsheets in quite a few of my files. "Dave Peterson" wrote: I think if you look closely, you misspelled the name of the new worksheet. Just a tip...when you do this, select a small range first. Then if you make a mistake, you'll only have to dismiss a few dialogs. Barb wrote: With the upgrade in Excel, I can no longer use 'Find and Replace All' to change a spreadsheet referenced in my VLOOKUP statements (everything is in one file). The 'old' and 'new' spreadsheets are identical except for the data and number of lines, columns and headings are the same. Now, two windows appear to select the file and worksheet for each occurance of the formula - which happens hundreds of times. Previously, I would highlight the column containing the VLOOKUP formula, select 'Find', enter the old sheet name and new sheet name, and select 'Replace All'. -- Dave Peterson |
#4
|
|||
|
|||
LOOKUP: change referenced spreadsheet
I ain't giving up.
Check for extra spaces in that worksheet name (maybe leading or trailing)??? And the new worksheet already exists, right??? Barb wrote: Thanks for the suggestion, but the names are spelled correctly. I am merely changing an FY05 to an FY06 - the rest of the name remains the same. I do test a small sample of six cells, four of which contain the formula in question. (The other two sum the rows.) The 'Find and Replace All' worked fine before updating to Excel 2003. Any other suggestions would be grealy appreciated, as I have relied on this form of updating spreadsheets in quite a few of my files. "Dave Peterson" wrote: I think if you look closely, you misspelled the name of the new worksheet. Just a tip...when you do this, select a small range first. Then if you make a mistake, you'll only have to dismiss a few dialogs. Barb wrote: With the upgrade in Excel, I can no longer use 'Find and Replace All' to change a spreadsheet referenced in my VLOOKUP statements (everything is in one file). The 'old' and 'new' spreadsheets are identical except for the data and number of lines, columns and headings are the same. Now, two windows appear to select the file and worksheet for each occurance of the formula - which happens hundreds of times. Previously, I would highlight the column containing the VLOOKUP formula, select 'Find', enter the old sheet name and new sheet name, and select 'Replace All'. -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
LOOKUP: change referenced spreadsheet
Thanks for not giving up!
I thought that I had checked the blanks, but there was a trailing one! I am happy once again! Thank you! "Dave Peterson" wrote: I ain't giving up. Check for extra spaces in that worksheet name (maybe leading or trailing)??? And the new worksheet already exists, right??? Barb wrote: Thanks for the suggestion, but the names are spelled correctly. I am merely changing an FY05 to an FY06 - the rest of the name remains the same. I do test a small sample of six cells, four of which contain the formula in question. (The other two sum the rows.) The 'Find and Replace All' worked fine before updating to Excel 2003. Any other suggestions would be grealy appreciated, as I have relied on this form of updating spreadsheets in quite a few of my files. "Dave Peterson" wrote: I think if you look closely, you misspelled the name of the new worksheet. Just a tip...when you do this, select a small range first. Then if you make a mistake, you'll only have to dismiss a few dialogs. Barb wrote: With the upgrade in Excel, I can no longer use 'Find and Replace All' to change a spreadsheet referenced in my VLOOKUP statements (everything is in one file). The 'old' and 'new' spreadsheets are identical except for the data and number of lines, columns and headings are the same. Now, two windows appear to select the file and worksheet for each occurance of the formula - which happens hundreds of times. Previously, I would highlight the column containing the VLOOKUP formula, select 'Find', enter the old sheet name and new sheet name, and select 'Replace All'. -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
LOOKUP: change referenced spreadsheet
And my faith in excel is restored!
Barb wrote: Thanks for not giving up! I thought that I had checked the blanks, but there was a trailing one! I am happy once again! Thank you! "Dave Peterson" wrote: I ain't giving up. Check for extra spaces in that worksheet name (maybe leading or trailing)??? And the new worksheet already exists, right??? Barb wrote: Thanks for the suggestion, but the names are spelled correctly. I am merely changing an FY05 to an FY06 - the rest of the name remains the same. I do test a small sample of six cells, four of which contain the formula in question. (The other two sum the rows.) The 'Find and Replace All' worked fine before updating to Excel 2003. Any other suggestions would be grealy appreciated, as I have relied on this form of updating spreadsheets in quite a few of my files. "Dave Peterson" wrote: I think if you look closely, you misspelled the name of the new worksheet. Just a tip...when you do this, select a small range first. Then if you make a mistake, you'll only have to dismiss a few dialogs. Barb wrote: With the upgrade in Excel, I can no longer use 'Find and Replace All' to change a spreadsheet referenced in my VLOOKUP statements (everything is in one file). The 'old' and 'new' spreadsheets are identical except for the data and number of lines, columns and headings are the same. Now, two windows appear to select the file and worksheet for each occurance of the formula - which happens hundreds of times. Previously, I would highlight the column containing the VLOOKUP formula, select 'Find', enter the old sheet name and new sheet name, and select 'Replace All'. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
How do I change thousands to intergers on my spreadsheet? | Excel Discussion (Misc queries) | |||
How to change case on a spreadsheet from Caps to Sentence? | Excel Discussion (Misc queries) | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Automatic date change when spreadsheet changes | Excel Worksheet Functions |