Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shading and Value Edits and Screen Scraping (WinME

    Used to use Excel endlessly then retired nearly 4 years ago and have forgotten (or perhaps remember badly) how to change the degree of shading in selected cells (one can do this, can't one?) and how to make a list of values that can be selecte for cells (like a drop down menu in applications). I can't seem to find the right keywords to find information on either of these subjects in Help (or in Woody's Que book, Using Microsoft Office XP. Perhaps I'm having (another) senior moment and you can't do either of these things.

    What I want to do is to use shading of different values to highlight columns in a worksheet that will be printed on a b&w printer. In the same spreadsheet, I want to enter a list of names that can be entered in cells in a column (actually I want to do this with different list values in several cells). In one case, upon choosing from the list of names in one cell, I want to lookup and insert and associated value in the cell in the next column to the right. -- think of this as selecting an employee name and displaying that in the column searched and automatically displaying the associated social security number in the adjoining cell in the next column to the right..

    This is a worksheet that will be customized to several different work groups (custom names and associated numbers) in my organization; so I will have to be able to set up the names and associated numbers.

    Finally, I'd like to know how to 'screen scrape' data displayed from a mainframe application and place it selectively into columns in a worksheet. I can highlight data, cut, and paste it; but I don't know how to (whether it is possible to) place different portions of the scraped data into different cells in the work sheet. The data on the scraped screen are arranged in regularly arranged columns layed out with 3270-type. Ideally, I will get data by scraping the screen and place it in the spreadsheet and other columns will be populated by looking up the associated data. I'm not averse to trying to do a little VBA or whatever, but I'm certainly not an expert.

    Thanks much, guys. Your knowledge, expertise, patience and willingness to share and help others is phenomenal. Perhaps someday I'll know enough to contribute something more than questions.

    All suggestions greatly appreciated and gratefully received.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shading and Value Edits and Screen Scraping (WinME

    The term for shading depending on the cell value is Conditional Formatting. You'll find it in the Format menu.
    The term for limiting entry in a cell to a list (or otherwise) is Validation. You'll find it in the Data menu.
    To look up data in another table, you can use (for example) the VLOOKUP worksheet function, or a combination of MATCH and INDEX.
    I'm not sure what you mean by "screen scraping". Perhaps the Text to Columns feature in the Data menu?

    All of the above can be looked up in the online help. Post back with details if you have more questions.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading and Value Edits and Screen Scraping (W

    Thank You, Hans.

    Your response is very much appreciated. I was able to set up the data validation rules and look up the associated values and place them in the cells where needed. However, I still need help with a coupla things.

    First, when I copied the VLOOKUP function formula into the cells that will be populated based on entries in other cells, #N/A appears if there is no entry in the cells where entries are to be made. For the life of me I cannot come up with the syntax needed to display nothing in these cells unless there are entries in dependent cells. I used the following expression { =IF(E6=" "," ",VLOOKUP(E6,CODES,2))} where braces are used for clarity and cell E6 is where I expect to enter a value that will be used to search the range CODES and return the value in the next cell of the row where the entered value is found. I also experimented with the expression =IF(ISBLANK(E6), " ",VLOOKUP(E6,CODES,2) and got no better result. What am I not understanding, here?

    Your suggestion to use the Data commands for my screen scraping application I haven't tried yet; however, I thought I'd try to explain the "screen scraping" term. Before PC's were prevalent, mainframe computer systems used 'dumb' terminals (sometimes called 'green screens'). PC's now have cards that accept and convert the mainframe display information so that it can be seen on the PC monitor. I need to be able to select and copy the data displayed by the mainframe application (I think it will be ASCII format) then paste it into a spreadsheet (EXCEL, of course). There will be no row-column coordinates in the 'scraped' data, but I will need to parse it to put various components of each row of the scraped data into specific cells of the ultimate spreadsheet. Hope this explains the question and the problem better.

    Thank you again for all the great support.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shading and Value Edits and Screen Scraping (W

    There are several recent threads about hiding #N/A for VLOOKUP, see for example the one starting at <post#=402650>post 402650</post#>.

    If you copy ASCII data into a spreadsheet, it will probably all be in the first column. You can then use Data | Text to Columns to parse the data into several columns. You will be given the opportunity to specify how.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading and Value Edits and Screen Scraping (W

    Bob

    Have you looked at Data / Get External Data for retrieving mainframe data?
    You may want to use this to get raw data into a new worksheet, then run some vba code
    to extract different portions (columns or rows or ranges) into your other worksheets

    HTH

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading and Value Edits and Screen Scraping (W

    Hello Hans, and thank you once again for the response.

    I looked at the thread and copied the example into my spreadsheet and substituted values where you showed the ellipses (I inserted {E3,CODES,2} -- without braces, directly from the VLOOKUP forumula in a cell that does the lookup correctly -- for each ellipsis) and still got errors. I counted parentheses of each flavor and found an unequal number in the example in the referenced post -- 4 lefts, 3 rights. Is this the problem? I tried placing another right parenthesis at the end and still got an error. I must not understand the syntax. My exact entry in cell F3 is . . . {=IF(ISNA(VLOOKUP(E3,CODES,2),"",VLOOKUP(E3,CODES, 2)))} -- braces not included -- after adding the 4th right parenthesis. The CODES value is the name of the range that VLOOKUP searches and the returned value is in the second column of the target row. The VLOOKUP works when I enter a value in column E that exists in the first column of the CODES range. It is written in F3 as follows: =IF(E3=" ", " ", VLOOKUP(E3,CODES,2)) however if I delete cell E3 the result is #N/A in cell F3. Does this make sense to you?

    Also, your example shows the indicator for null values to be used if the function returns #N/A as 2 double quotes with no space between them. Am I reading this correctly? Should there be a space between the double quotes? Should they be single quotes?

    I've never had this much trouble with formulae before. Could I have set some variable in Excel that is causing the problem though not obvious from the results I'm getting?

    I haven't tried the Data functions on scraped ASCII data yet as I must scrape at work and sent it to my home PC to experiment.
    More to come when I venture down that path.

    Again, thank you very much for your help.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading and Value Edits and Screen Scraping (W

    No, I haven't tried this yet. I failed to send the Excel file I created at work to my home PC and I can't get into that file or that application from home. I'll be sure to scrape enough different types of screens to experiment with this when I go back to work Monday evening.

    Thank you for the suggestion. I'll let you know how it goes. I'm certain I'll need to ask more questions.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shading and Value Edits and Screen Scraping (W

    My "formula" in the other thread was just a general indication, not a real formula, but you are correct that there is a parenthesis missing in it. It should be like this:

    =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

    so the extra closing parenthesis is not at the end, but after the first VLOOKUP(...). If the VLOOKUP formula fails, i.e. returns #N/A, the formula returns an empty string. Two double quotes without anything in between represent an empty string; it does not even contain a space. This is the nearest you can get in a formula to leaving a cell blank.

    Happy scraping.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shading and Value Edits and Screen Scraping (W

    That worked, Hans! Thank you!

Posting Permissions

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