Posts by AAE

  • Conditionally show zero or last two characters of text string

    Re: Conditionally show zero or last two characters of text string


    AllieExcel


    Note the new title I've given your thread - in future threads try to be more precise in describing your thread, keeping in mind thread titles are used to search the forum. Accurate well-written titles return more relevant search results.


    Quote

    Please tell me what I am missing . . .
    =IF(A24=RIGHT(A24,LEN(A24)-FIND("("-")OR(" ")",A24,1)),0,RIGHT(A24,2))


    A24 will never be equal to a part of its data string. Example: if A24 contains "Yellow Submarine" it will never be equal to "marine" (assuming this the string portion returned from the RIGHT formula). The equal operator demands both sides of the equation be exactly the same.


    The arguments in the OR function must evaluate to either true or false. This function could be nested in the IF statement, but only if you are testing for true or false conditions returned by the OR function.


    Highly recommended: learn to use the formula evaluation tool in Excel. By stepping through formulas you can see how Excel evaluates each argument and the result that is returned. It is a great learning tool.


    You should post either a sample workbook (dummy data) or give examples of the data strings along with the expected/desired results and explain your logic clearly.

  • Change cell background color based on another cells background color

    Re: Change cell background color based on another cells background color


    Queo,


    Welcome to Ozgrid.
    Maybe you haven't read the Forum Rules yet? Please do so, especially noting the rule on cross posting.
    All Excel forums have the same rule on cross posting and for good reason, all of which is well-explain in Message to Cross Posters (click link in my signature).


    -----------------------------------------
    After you have updated your post with links to the cross post someone will chip in on a solution.

  • Create Room Reservation System

    Re: Create Room Reservation System


    simplethings,


    Welcome to Ozgrid.
    Please note that posting your questions in thread started by others is known as thread hijacking.
    Always start a new thread for your questions and if it helps to clarify your needs you may include to any other thread.


    This thread is quite old and is being closed.

  • MsgBox / VLookUp

    Re: MsgBox / VLookUp


    saikong051,


    Welcome to Ozgrid.
    Please read the Forum Rules and learn to use code tags.
    All VBA code must be wrapped in codes, which you did not use. I've added them for this time only.


    When posting VBA code do not use any font formatting on the code - the code tags will override all other formatting.


    To quickly add code tags highlight all of the code then click the button with the # symbol.

  • Very Large File - why?

    Re: Very Large File - why?


    Clarifying PCI's post:


    You must delete the rows/columns by highlighting them and using the right-click to display the context menu then choosing delete.
    Deleting with the delete key does not reset the last cell.


    My method:
    Press Ctrl + End to go to the last used cell
    Select all row or column headers
    Right-click to get the pop up menu > delete
    Immediate save

  • Messaging all users currently using Shared Workbook

    Re: Messaging all users currently using Shared Workbook


    y0zer,


    Welcome to Ozgrid.


    Posting your question in threads started by other is known as thread hijacking - - always start a new thread for your questions.
    If it is helpful to clarify your needs you may always include a link to any other thread in the forum.


    Start a new thread for your question.

  • Automate copying rows based on counter number increment

    Re: Can this be automated


    larbec,


    By now you are, surely, aware that thread titles are used to search the forum and they should accurately define the thread - that is, the title will facilitate obtaining good search results. Try a search now using your current title and see if you get any relevant links.


    Edit your first post, Go Advanced, and give thread an accurate descriptive title.
    Suggested thought: Automate copying rows based on counter number increment.

  • Calculate percentage based on the cell values Y/N/NA

    Re: Calculate percentage based on the cell values Y/N/NA


    Hello Pam.


    Welcome to Ozgrid.


    I think you need to more clear about the percentage values.
    Is the percentage values applicable only when "Y" is selected from the drop down? What percentage is applied if the selection is "N" or "NA"?


    Maybe this will get you started


    =SUM(IF(B2="Y",50,0),IF(C2="Y",30,0),IF(D2="Y",20,0))

  • Sub-categories within an Exel pie chart

    Re: Sub-categories within an Exel pie chart


    Hi Konuku,


    Welcome to Ozgird.


    Most seasoned Excel users and those involved with data visualization will tell you to avoid pie charts as they are not the best choice for showing comparisons. Beyond 3-4 data points piece charts become useless. If you must use them make sure to only use the 2D type so as not to distort the proportions of the slices.


    A stacked column chart may be a better option


    You could also use a clustered column chart and plot the three subgroups on the primary axis and break out the largest sub-group values and plot them on the secondary axis in the same chart.


    If you must stick with pie charts, you can still use a separate pie chart for the largest sub-group and just format the main chart and break out chart to remove borders, position legends, data labels, etc and then adjust the layout to make them appear as one contiguous chart.


    How to Select the Right Chart for Your Data

  • Search workbook and activate sheet where value is found

    Re: Search workbook and activate sheet where value is found


    epichunter,


    We're happy yo have you on board at Ozgrid.
    Thread titles are used in searching the forum; well-developed title that accurately define the thread will return much more relevant results when searching.


    Take note of the new title I've given to your thread as an example and in future thread give more thought to developing a descriptive title that facilitates searching.


    -------------------------------------------


    Your workbook design, to be honest, is a real disaster and you're only making it much more difficult for yourself and others with the current approach.


    Data and reports should always be kept on separate sheets.
    The data sheet should always be in a classical database structure which means first row is a header row with the header cell using a descriptive text that identifies the type of data in the cells/ immediately below it.


    All rows below the header row are "records".


    Recommended reading: Excel Best Practices & Efficient Spreadsheet Design


    Quote

    This has to be the one as about 5 different people need to understand it. It is simple to follow for them (this is an age thing)


    A properly designed workbook will only help facilitate the searching and make it easier for other users.

  • VBA .Value cuts off ending 0's

    Re: VBA .Value cuts off ending 0's


    Cells are formatted as General until you change them. For numbers the default decimal place setting is two places.
    It is likely the default format is being applied which automatically drops trailing zeros.


    Suggest you either copy and pastespecial values & formatting or apply the format of choice to the range after pulling the values.

  • Return matching value from other sheet using drop down list

    Re: Return matching value from other sheet using drop down list


    cptnrsk,


    Welcome to Ozgrid.


    Posting your questions in threads started by others is known as thread hijacking.
    Always start a new thread for your questions and include a link to any other thread you think is relevant in helping to clarify your needs.


    Thanks.

  • Count entries within specific date range

    Re: Count entries within specific date range


    Javed,


    Glad to have you here at Ozgrid.
    Another moderator has commented directly in your post about thread titles.
    Take note of the new title I've given your thread as an example of a good thread title.


    See the Excel Help on the COUNTIFS function.
    Here is a basic example


    =COUNTIFS(Sheet2!A2:A100,">=1/1/2014",Sheet2!A2:A100,"<=3/31/2014")


    To make this formula more dynamic you could replace the hard-coded dates with a cell reference. This would allow you to simply enter new date ranges and update the count without having to rewrite the formula or create another formula.


    =COUNTIFS(Sheet2!A2:A7,">="&A1,Sheet2!A2:A7,"<="&B1), where A1 and B1 contain date values

  • Locking the worksheet to A1

    Re: Locking the worksheet to A1


    You might also try using worksheet protection.
    Set A1 locked property to unlocked, apply sheet protection and uncheck the box "Select Locked Cells".
    This will prevent the user from moving to other cells on the sheet.


    You may also hide all columns and rows that you do not plan to use and maybe limit the visible screen area.
    You can also use the Excel options to turn off the horizontal and vertical scroll bars.


    Not certain in your case, but I think you may need at least cell A2 to be unlocked as well. Some scanners will send the equivalent of an "Enter key" after the scan which forces an exit from edit mode. If you have issues with this unlock cell A2 or just do a test scan before protecting the sheet and confirm the behavior of the scanner.