+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Bonnie
    Guest

    !!!!!!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. #2
    Dave O
    Guest

    Re: !!!!!!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. #3
    Elkar
    Guest

    RE: !!!!!!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. #4
    Bonnie
    Guest

    Re: !!!!!!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. #5
    Bonnie
    Guest

    RE: !!!!!!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. #6
    Elkar
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1