A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 20th, 2006, 07:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default !!!!!!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  
Old January 20th, 2006, 07:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default !!!!!!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  
Old January 20th, 2006, 07:57 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default !!!!!!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  
Old January 20th, 2006, 10:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default !!!!!!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  
Old January 20th, 2006, 10:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default !!!!!!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  
Old January 20th, 2006, 10:53 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default !!!!!!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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 06:59 PM
Generating excel combinations mark4006 General Discussion 2 March 6th, 2005 05:40 PM
simple columns won't add Phil Worksheet Functions 3 October 5th, 2004 11:04 PM


All times are GMT +1. The time now is 06:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.