!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution...

[Guest]

!!!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!!!!

 

[Dave O]

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.

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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?

 

[Guest]

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