Microsoft Excel

Tags:
Microsoft Excel
microsoft excel functions
Gents: Could you please assist me in the below case: 1-I have 2 Excel sheet on my PC (Excel A is extracted on a daily basis from I Series by using data transfer while Excel B is the target one). 2- Excel B (target) has a special header and colors and contains 4 columns and 20 rows with zeros amount in each cell 3-Excel (A) has the same columns and rows, but has amount in each cell. 4-I referred every cell in Excel (B) to the concerned cell in Excel (A) on my PC. 5-Now I have got my target excel B. 6-Every day I replaced Excel (A) with a new extract from I series , and when I open it , the Excel (B) effected with the new amount of Excel A (according to my previous reference). My requirement is there any method to let another user to use those 2 Excel files on his PC without changing the reference predefined in Excel (B) Your assist in this regard will be appreciated. Tarek

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 19  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Senior1systemanalyst
    any idea about this case.please supported.tarek
    260 pointsBadges:
    report
  • TomLiotta
    Asking the same question again doesn't help. Is there a problem with giving the two Excel files to another user? What problem is happening? I don't quite understand why you are using two files instead of just one in the first place. Why do you have separated? -- Tom
    125,585 pointsBadges:
    report
  • Senior1systemanalyst
    Hi Tom,
    my problem is when any other user follow the same steps on his PC  he will need to replace Excel (A) with a new extract from I series (step # 6) and when  he open Excel (A) it  should be effected in Excel (B) . (according to my previous predefined cells on my PC,  i mean cell location reference )
     i have to change all cell reference in excel (B) to be matched with the new location of Excel A .
    my kindly request is there any method to let another user to use the same scenario on his PC without changing the reference predefined in Excel (B)
    Thanks....
    tarek   
    260 pointsBadges:
    report
  • Senior1systemanalyst
    Normal 0 false false false EN-US X-NONE AR-SA MicrosoftInternetExplorer4 Regarding why I use two files instead of just one in the first place. Because of the goal of using Excel file (A) is only to be extracted automatically from I series (by using data transfer utility) which extract data from As/400 into normal and standard of Excel file …. While Excel B (the target file) contains special headers, so I need for 2 files (one as standard excel file and the second consider as container with a special header)    I have no method to extract data from AS/400 directly to the target file … if you? Please give me your advice. Thanks - Tarek
    260 pointsBadges:
    report
  • philpl1jb
    Sounds like your references have the user name in them.If you were to make a folder C:\Extract and put both files in there and change the references to C:\Extract couldn't they be copied to any computer and put into the folder C:\Extract?Phil  
    51,355 pointsBadges:
    report
  • Senior1systemanalyst
    Hi Phil, Perfect exactly as what I need, but I couldn’t understand the solution well. What you mean by “put into the folder C:\Extract?Phil” Do you men to replace? Phil   by user name?   Thanks-tarek 
    260 pointsBadges:
    report
  • philpl1jb
    not at all .. that was the editor messing with my response. Put the files in C:\Extract      Change the references to point at the other excel file in c:\Extract   Make the same folder on any client machine   and put the two files there.   Phil
    51,355 pointsBadges:
    report
  • philpl1jb
    The user name shouldn't be in the file name or the path or the reference.
    51,355 pointsBadges:
    report
  • philpl1jb
    I think that the reference cannot point a location on a client machine that doesn't exist, a location that includes the user id in the path.  But it's been a while since I've done this and so I cannot be sure what your references look like.
    51,355 pointsBadges:
    report
  • Senior1systemanalyst
    Hi Phil, You gave me an excellent guide. if I put 2 Excel files on a folder at my Desktop I found that each cell will contains user ID, while if I put the 2 files on c:\extract (as you advised me)  I won’t need to change anything. Just I will let the other users to create the same folder (c:\extract) on his PC. One only thing .if the user open the 2 excel files the file B (target file) will be  affected with the data in file A Is there any method to let the data in file (A) affected in file (B)  without opening the 2 files. Many thanks ya man-Tarek
    260 pointsBadges:
    report
  • philpl1jb
    I don't think you need to open the extract file.  Just open the file with the formulas.  Phil
    51,355 pointsBadges:
    report
  • Senior1systemanalyst

    Hi Phil,

    I have tried this, but it doesn’t affect.

    I should open the extracted file (A) to let the target file (B) affected.

    If anyone has a solution in this regard will be appreciated.

    Thanks-Tarek

     

    260 pointsBadges:
    report
  • TomLiotta
    I hoped Phil had it answered because I still can't understand why the data isn't loaded directly into Excel B. Having two files seems to complicate this without reason.   Because of the goal of using Excel file (A) is only to be extracted automatically from I series...   That is already understood. But why isn't the goal to have Excel B extract from iSeries and also to present the data. Why is extraction and presentation broken into two files? It can be done in one file, unless there is some undisclosed reason for separating them.   In fact, the whole business problem is not described yet. What I understand is that there is a set of data that should be extracted from iSeries into an Excel spreadsheet. The extraction should run for any user who has the Excel file, and the data should be presented in the format that the Excel file describes.   What other business requirements exist beyond that? Why must it be done with two Excel files instead of one?   I can imagine a possible reason, but solutions shouldn't be developed based on guesses if a true reason exists.   Tom
    125,585 pointsBadges:
    report
  • Senior1systemanalyst
    Normal 0 false false false EN-US X-NONE AR-SA MicrosoftInternetExplorer4 I appreciate your attention. I have explained the reason of use 2 excel file, however I will try to be more clarify. The target excel file (B) is like a template file contains special headers, colors, subtotals, totals etc… I couldn’t defined the same template file while I extracted the data from I series  In other words when I extracted directly from I series to the target excel file (B) the file became completely different, so I did this workaround. Tarek /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}
    260 pointsBadges:
    report
  • TomLiotta
    I couldn’t defined the same template file while I extracted thedata from I series   Why not?   That's what hasn't been explained. So far you've only said you couldn't do it. What problem did you run into? Why can't you do it in Excel B the same way you do it in Excel A? (Either the same way or a slightly modified way.)   What stops you from doing it? Did you try? What happened?   I don't know enough about Excel B to understand the problem since I don't have Excel B to test. Nor do I have data from your AS/400, so you need to describe elements of the problem that we can't see.   Tom Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Sorry, I just finally understood your last paragraph a little better. (The editor doesn't show these comments well.)   when I extracted directly from I series to the target excel file (B) the file became completely different...   Does that mean that the extracted data was different? Or that the presentation was different, e.g., different colors or fonts? Or that it generated a different file?   Tom
    125,585 pointsBadges:
    report
  • Senior1systemanalyst
    Normal 0 false false false EN-US X-NONE AR-SA MicrosoftInternetExplorer4 I don’t know if you aware by data transfer?  Also if you aware by template file?The number of columns in each file is same.Could you let me know how I can extract data from I series into template Excel file?-Tarek /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}
    260 pointsBadges:
    report
  • TomLiotta
    Yes, I am aware of both iSeries transfers and Excel templates (.XLT files). Today is your first mention that Excel B is a "template". Is it actually a "template" or do you only want to use it "like a template"?   Tom
    125,585 pointsBadges:
    report
  • Senior1systemanalyst
    Is it actually a “template” or do you only want to use it “like a template”? what is the difference?tarek
    260 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following