If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution...
!!!PLEASE HELP ME!!! I have a big excel file set up where each row
represents a specific city (this file is the "main file"). Then I have a seperate excel file set up for each city. I enter data into the main file and each cell feeds into the appropriate city excel file. The problem I've run into is that the cities are listed alphabetically and every month or so I need to add a new city (row) onto the main excel file - when I do that all of the rows below where I added the new row are now feeding into the wrong excel file. EXAMPLE: If I have the following cities listed on the main excel file Row 1 Austin Row 2 Birmingham Row 3 Columbus And I have seperate excel files named Austin, Birmingham, and Columbus. Austin excel file pulls data from row 1 of the main file, Birmingham file pulls data from row 2 of the main file and Columbus pulls from row 3. If I were to add Boston then it would change my main file to: Row 1 Austin Row 2 Birmingham Row 3 Boston Row 4 Columbus Now the Columbus excel file that is pulling data from row 3 off the main file is pulling the data for the city Boston instead of Columbus. HOW CAN I FIX THIS?!?! PLEASE HELP ME!!!! |
#2
|
|||
|
|||
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution...
Here's what happened: your Main file acts as source data for all the
city files. The city file references a specific cell in the Main file. If you insert a row in the Main file when the city file is open, the formula in the city file will change too. If the city file is not open, the city file will not be aware that a change has been made, so it will look in the old location. To keep everything current, before you insert a row for Boston (to continue your example), you would need to open the Columbus file and all the rest of the city files that follow it in the list. Ugh. Fixing it will be a bit of a project, because you'll need to open each file and make corrections. However, if you use the same type of direct cell reference, you'll need to do that "open each file in the list" thing every time you insert a row. A better idea would be to set up the City files with a VLOOKUP formula, or some such, that will dynamically review the Main file and pull the correct city data regardless of its location in the list. This may be a blessing in disguise, because since city names can repeat from state to state (there are 121 towns called Springfield, more than 1 per state). A dynamic formula like VLOOKUP, if it's set up right, will not need to change after rows are inserted in Main. This is bad news to deliver on a Friday, sorry 'bout that. |
#3
|
|||
|
|||
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution...
I would suggest adding a column to the beginning of your "main file" and
storing a unique ID Number for each city in that column. Then, use the VLOOKUP function in each of your City Files to look up that ID Number in the Main File. This way, the order of your cities in the Main File won't matter. Plus, you'll be able to have two cities with the same name and there won't be any conflicts. HTH, Elkar "Bonnie" wrote: !!!PLEASE HELP ME!!! I have a big excel file set up where each row represents a specific city (this file is the "main file"). Then I have a seperate excel file set up for each city. I enter data into the main file and each cell feeds into the appropriate city excel file. The problem I've run into is that the cities are listed alphabetically and every month or so I need to add a new city (row) onto the main excel file - when I do that all of the rows below where I added the new row are now feeding into the wrong excel file. EXAMPLE: If I have the following cities listed on the main excel file Row 1 Austin Row 2 Birmingham Row 3 Columbus And I have seperate excel files named Austin, Birmingham, and Columbus. Austin excel file pulls data from row 1 of the main file, Birmingham file pulls data from row 2 of the main file and Columbus pulls from row 3. If I were to add Boston then it would change my main file to: Row 1 Austin Row 2 Birmingham Row 3 Boston Row 4 Columbus Now the Columbus excel file that is pulling data from row 3 off the main file is pulling the data for the city Boston instead of Columbus. HOW CAN I FIX THIS?!?! PLEASE HELP ME!!!! |
#4
|
|||
|
|||
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution...
Thank you so much!!! I really appreciate your help! I've spent WAY too much
time trying to figure this out already! It will be a pain to open every single city listed b/c there is soon to be more than 100 listed. So, if I could be such a pest to ask how I would go about setting up a VLOOKUP that would be great! If it's detailed instructions I'll look it up in the Microsoft Excel Book. Thanks again! "Dave O" wrote: Here's what happened: your Main file acts as source data for all the city files. The city file references a specific cell in the Main file. If you insert a row in the Main file when the city file is open, the formula in the city file will change too. If the city file is not open, the city file will not be aware that a change has been made, so it will look in the old location. To keep everything current, before you insert a row for Boston (to continue your example), you would need to open the Columbus file and all the rest of the city files that follow it in the list. Ugh. Fixing it will be a bit of a project, because you'll need to open each file and make corrections. However, if you use the same type of direct cell reference, you'll need to do that "open each file in the list" thing every time you insert a row. A better idea would be to set up the City files with a VLOOKUP formula, or some such, that will dynamically review the Main file and pull the correct city data regardless of its location in the list. This may be a blessing in disguise, because since city names can repeat from state to state (there are 121 towns called Springfield, more than 1 per state). A dynamic formula like VLOOKUP, if it's set up right, will not need to change after rows are inserted in Main. This is bad news to deliver on a Friday, sorry 'bout that. |
#5
|
|||
|
|||
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution...
Thanks so much for your help! Does that mean that in the formula in the
sheet that is pulling data I list the ID # instead of the row # and it will know what I'm talking about? Will I need to add any other symbols to make that work? "Elkar" wrote: I would suggest adding a column to the beginning of your "main file" and storing a unique ID Number for each city in that column. Then, use the VLOOKUP function in each of your City Files to look up that ID Number in the Main File. This way, the order of your cities in the Main File won't matter. Plus, you'll be able to have two cities with the same name and there won't be any conflicts. HTH, Elkar "Bonnie" wrote: !!!PLEASE HELP ME!!! I have a big excel file set up where each row represents a specific city (this file is the "main file"). Then I have a seperate excel file set up for each city. I enter data into the main file and each cell feeds into the appropriate city excel file. The problem I've run into is that the cities are listed alphabetically and every month or so I need to add a new city (row) onto the main excel file - when I do that all of the rows below where I added the new row are now feeding into the wrong excel file. EXAMPLE: If I have the following cities listed on the main excel file Row 1 Austin Row 2 Birmingham Row 3 Columbus And I have seperate excel files named Austin, Birmingham, and Columbus. Austin excel file pulls data from row 1 of the main file, Birmingham file pulls data from row 2 of the main file and Columbus pulls from row 3. If I were to add Boston then it would change my main file to: Row 1 Austin Row 2 Birmingham Row 3 Boston Row 4 Columbus Now the Columbus excel file that is pulling data from row 3 off the main file is pulling the data for the city Boston instead of Columbus. HOW CAN I FIX THIS?!?! PLEASE HELP ME!!!! |
#6
|
|||
|
|||
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution...
Yes, it should look something like this:
=VLOOKUP(1234,[filename.xls]Sheet1!$A$1:$C$100,3,False) Where 1234 is the City's ID Number [filename.xls] is your Main File Sheet1!$A$1:$C$100 is the range your data is in 3 is the column of the results you want returned False tells Excel to find an Exact match only HTH, Elkar "Bonnie" wrote: Thanks so much for your help! Does that mean that in the formula in the sheet that is pulling data I list the ID # instead of the row # and it will know what I'm talking about? Will I need to add any other symbols to make that work? "Elkar" wrote: I would suggest adding a column to the beginning of your "main file" and storing a unique ID Number for each city in that column. Then, use the VLOOKUP function in each of your City Files to look up that ID Number in the Main File. This way, the order of your cities in the Main File won't matter. Plus, you'll be able to have two cities with the same name and there won't be any conflicts. HTH, Elkar "Bonnie" wrote: !!!PLEASE HELP ME!!! I have a big excel file set up where each row represents a specific city (this file is the "main file"). Then I have a seperate excel file set up for each city. I enter data into the main file and each cell feeds into the appropriate city excel file. The problem I've run into is that the cities are listed alphabetically and every month or so I need to add a new city (row) onto the main excel file - when I do that all of the rows below where I added the new row are now feeding into the wrong excel file. EXAMPLE: If I have the following cities listed on the main excel file Row 1 Austin Row 2 Birmingham Row 3 Columbus And I have seperate excel files named Austin, Birmingham, and Columbus. Austin excel file pulls data from row 1 of the main file, Birmingham file pulls data from row 2 of the main file and Columbus pulls from row 3. If I were to add Boston then it would change my main file to: Row 1 Austin Row 2 Birmingham Row 3 Boston Row 4 Columbus Now the Columbus excel file that is pulling data from row 3 off the main file is pulling the data for the city Boston instead of Columbus. HOW CAN I FIX THIS?!?! PLEASE HELP ME!!!! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help! Looking for Simple Solution in Layman Terms | Judy | General Discussion | 1 | October 22nd, 2005 06:13 PM |
Help with what should be a simple formula | B Millar via OfficeKB.com | Worksheet Functions | 2 | June 16th, 2005 04:18 PM |
Outlook bug: views do not work after simple find. | N. P. | General Discussion | 1 | March 11th, 2005 05:59 PM |
Generating excel combinations | mark4006 | General Discussion | 2 | March 6th, 2005 04:40 PM |
simple columns won't add | Phil | Worksheet Functions | 3 | October 5th, 2004 11:04 PM |