eCharcha.Com   Support eCharcha.Com. Click on sponsor ad to shop online!

Advertise Here

Go Back   eCharcha.Com > Science and Technology > How to

Notices

How to Stuck for an answer to "How to do .."?

Reply
 
Thread Tools Display Modes
  #1  
Old January 21st, 2010, 11:04 AM
Rakhi's Avatar
Rakhi Rakhi is offline
Senior eCharchan
 
Join Date: Jan 2009
Posts: 9,702
Rakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond repute
Excel help...

Guys, i am sorry, I seem to have too many questions today. If possible please help me with this.

I am not even sure if I can do it myself or i will need IT help. Here is the problem.

1. I download an excel file containing a few details from an external system (file 1).
2. I open another excel file (file 2) which contains a specific formula (sum) and paste the above down loaded data in same tab which contains the formula.

a) In file 2, I have a formula validating specific rule in a specific column
=SUMIF(D: D,"xyz",I:I)


How do I perform a calculation without manually copying the file 1 data into file 2? I know its possible...but I don't know how. Will I need VB etc or is there a simple way to include this criteria? I mean...I can save the file 1 in a specific location. Now how should I use the formula to pull the same details I used in file 2 =SUMIF(D: D,"xyz",I:I)? How...? Column D and column I details are the ones I copied and pasted manually from the file 1.

I hope I mentioned the problem correctly.

Please help me if you can. thank you so much.

Last edited by Rakhi; January 21st, 2010 at 11:09 AM. Reason: spelling
Reply With Quote
  #2  
Old January 21st, 2010, 11:16 AM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: Excel help...

simply refer to the other spreadsheet after it has been saved. In the cell type the "=" sign .. then click on to the other spreadsheet and click on the cell you want to reference there. That should do it. Unless I didnt understand your request.
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #3  
Old January 21st, 2010, 11:22 AM
Rakhi's Avatar
Rakhi Rakhi is offline
Senior eCharchan
 
Join Date: Jan 2009
Posts: 9,702
Rakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by smellyfinger View Post
simply refer to the other spreadsheet after it has been saved. In the cell type the "=" sign .. then click on to the other spreadsheet and click on the cell you want to reference there. That should do it. Unless I didnt understand your request.
But your suggestion will be a manual calculation right Smelly....Right now as soon as i paste the data manually from the other excel, this particular cell with formula automatically picks up the data which I have pasted below. If I reference it the way you said, every time I need to change the data (which is twice a day), I need to open and perform this 'referencing'. Know what i mean?

I am looking for some thing like:

1. If I key in a particular date, the formula column should automatically point the file which I have saved AND then apply the sum formula.
This means I am saving the file in another location but not manually copying and pasting it.

I hope this is slightly clear now...
Reply With Quote
  #4  
Old January 21st, 2010, 11:26 AM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by Rakhi View Post
But your suggestion will be a manual calculation right Smelly....Right now as soon as i paste the data manually from the other excel, this particular cell with formula automatically picks up the data. If I reference it the way you said, every time I need to change the data (which is twice a day), I need to open and perform this 'referencing'. Know what i mean?

I am looking for some thing like:

1. If I key in a particular date, the formula column should automatically point the file which I have saved AND then apply the sum formula.
This means I am saving the file in another location but not manually copying and pasting it.

I hope this is slightly clear now...
If the file name is the same every time, it will still work. If the file name changes every tiem you save it, then you will need more advanced logic.

So one option will be to keep the file name the same. You can download the file as excel01212010-1.xls .. but then copy it to excelforreference.xls

When you get the second file, just overwrite the excelforreferrence.xls every time. That way you keep history but you only have one working file.
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #5  
Old January 21st, 2010, 11:38 AM
Rakhi's Avatar
Rakhi Rakhi is offline
Senior eCharchan
 
Join Date: Jan 2009
Posts: 9,702
Rakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by smellyfinger View Post
If the file name is the same every time, it will still work. If the file name changes every tiem you save it, then you will need more advanced logic.

So one option will be to keep the file name the same. You can download the file as excel01212010-1.xls .. but then copy it to excelforreference.xls

When you get the second file, just overwrite the excelforreferrence.xls every time. That way you keep history but you only have one working file.
Well, I can set the name and overwrite it every time I use. I tried your technique but its throwing an error saying the formula is incorrect Smelly.
This is the formula I am using..

='drive location\file location\file name.xls'!sumif(D,"xyz",I:I)

Not sure if this is what you mean by cell reference.

P:S: the details in column D and column I remain same. so I didnt change them.
Reply With Quote
  #6  
Old January 21st, 2010, 11:39 AM
razzrhino's Avatar
razzrhino razzrhino is offline
Senior eCharchan
 
Join Date: May 2006
Posts: 1,814
razzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond reputerazzrhino has a reputation beyond repute
Re: Excel help...

check the number of parantheses in your formula
__________________
"Don't Vote—It Just Encourages the Bastards." - P.J. O'Rourke
Reply With Quote
  #7  
Old January 21st, 2010, 11:39 AM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: Excel help...

The other option is to use a naming convention and the INDIRECT command.

1. Naming convention: Name your files based on date and serial number to make it easy. You dont have to do this step - you can type in the name of the spreadsheet directly.

2. In your calculation spreadsheet in one of the cells, either type in the name of the file you want to pull from .. or create a file name based on the naming convetion .. in say, cell A1

3. Use the formula

=SUMIF("'["&INDIRECT("A1") & "]Sheet1"& "'!B1:B5) ... dah dah

Will be easier if you just zipped up the two files and posted them here.
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #8  
Old January 21st, 2010, 11:42 AM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by Rakhi View Post
Well, I can set the name and overwrite it every time I use. I tried your technique but its throwing an error saying the formula is incorrect Smelly.
This is the formula I am using..

='drive location\file location\file name.xls'!sumif(D,"xyz",I:I)

Not sure if this is what you mean by cell reference.

P:S: the details in column D and column I remain same. so I didnt change them.

The sumif is outside

=SUMIF([Book2]Sheet1!$A$1:$A$17,"fff",[Book2]Sheet1!$B$1:$B$17)
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #9  
Old January 21st, 2010, 11:54 AM
motowner's Avatar
motowner motowner is offline
Senior eCharchan
 
Join Date: May 2006
Posts: 1,647
motowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by Rakhi View Post
Guys, i am sorry, I seem to have too many questions today. If possible please help me with this.

I am not even sure if I can do it myself or i will need IT help. Here is the problem.

1. I download an excel file containing a few details from an external system (file 1).
2. I open another excel file (file 2) which contains a specific formula (sum) and paste the above down loaded data in same tab which contains the formula.

a) In file 2, I have a formula validating specific rule in a specific column
=SUMIF(D: D,"xyz",I:I)


How do I perform a calculation without manually copying the file 1 data into file 2? I know its possible...but I don't know how. Will I need VB etc or is there a simple way to include this criteria? I mean...I can save the file 1 in a specific location. Now how should I use the formula to pull the same details I used in file 2 =SUMIF(D: D,"xyz",I:I)? How...? Column D and column I details are the ones I copied and pasted manually from the file 1.

I hope I mentioned the problem correctly.

Please help me if you can. thank you so much.
You can do the following

1) create an ODBC data source (DSN) to file1.
2) Open file2 and create a new sheet (say sheet_source)
3) Go to Data -> Import data from DSN
4) Point to the file1 DSN
5) In your calculation sheet, make all your formulas point to sheet_source. E.g. "=SUMIF(sheet_source!D, "xyz", sheet_source!I:I)"
Reply With Quote
  #10  
Old January 21st, 2010, 11:57 AM
Rakhi's Avatar
Rakhi Rakhi is offline
Senior eCharchan
 
Join Date: Jan 2009
Posts: 9,702
Rakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by razzrhino View Post
check the number of parantheses in your formula
Eh?

Quote:
Originally Posted by smellyfinger View Post
The other option is to use a naming convention and the INDIRECT command.

1. Naming convention: Name your files based on date and serial number to make it easy. You dont have to do this step - you can type in the name of the spreadsheet directly.

2. In your calculation spreadsheet in one of the cells, either type in the name of the file you want to pull from .. or create a file name based on the naming convetion .. in say, cell A1

3. Use the formula

=SUMIF("'["&INDIRECT("A1") & "]Sheet1"& "'!B1:B5) ... dah dah

Will be easier if you just zipped up the two files and posted them here.
OK...not sure. here is the files. I am giving only the required columns.

1. The data from 18 downwards is manually copied.
Attachment 13540

2. What should be my formula in cell I13 if I saved a file with the name "test" in c:foldername\?
Attachment 13541

P.S: I still need the sum function to work.

Thanks a lot! I really appreciate this.

Last edited by Rakhi; January 21st, 2010 at 12:45 PM.
Reply With Quote
  #11  
Old January 21st, 2010, 12:08 PM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: Excel help...

The formula for I13 is below

=SUMIF('C:\Documents and Settings\guest\Desktop\[test.xls]Test'!D,"xyz",'C:\Documents and Settings\guest\Desktop\[test.xls]Test'!I:I)

Just replace the pathname as appropriate. The only caveat is that the test.xls file needs to be open before you open the for EC.xls file.

__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #12  
Old January 21st, 2010, 12:29 PM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: Excel help...

There is a way around the test.xls file being open as well. I can tell you if you are interested.
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #13  
Old January 21st, 2010, 01:00 PM
Rakhi's Avatar
Rakhi Rakhi is offline
Senior eCharchan
 
Join Date: Jan 2009
Posts: 9,702
Rakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by smellyfinger View Post
There is a way around the test.xls file being open as well. I can tell you if you are interested.
OK...sure. But let me figure this out first
I am still stuck.
Reply With Quote
  #14  
Old January 22nd, 2010, 06:17 AM
Rakhi's Avatar
Rakhi Rakhi is offline
Senior eCharchan
 
Join Date: Jan 2009
Posts: 9,702
Rakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond reputeRakhi has a reputation beyond repute
Re: Excel help...

Quote:
Originally Posted by smellyfinger View Post
The formula for I13 is below

=SUMIF('C:\Documents and Settings\guest\Desktop\[test.xls]Test'!D,"xyz",'C:\Documents and Settings\guest\Desktop\[test.xls]Test'!I:I)

Just replace the pathname as appropriate. The only caveat is that the test.xls file needs to be open before you open the for EC.xls file.

I got it! I got it! I got it! I got it! I got it!
Thanks so much Smelly! You must be thinking that I am so dumb. It took me so long even after you put everything in the plate.
Reply With Quote
Reply

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
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

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Subbu SoapBox 2 July 28th, 2010 07:10 PM
excel chart help kkkk How to 14 March 5th, 2010 02:04 AM
Excel Help chitrala Computing 2 August 29th, 2009 08:41 AM
How to obtain +ve and -ve value in MS-Excel aashoo Friday Special 13 September 21st, 2007 01:19 PM
forecsting in excel? loverboy260 SoapBox 1 February 10th, 2003 10:29 AM


All times are GMT -7. The time now is 10:49 PM.


Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Site Copyright © eCharcha.Com 2000-2012.