Posts by Pratibha

    Hey I found out the answer, finally, by google search


    Just updating the post here for others.


    I may not be clear in my above post, but it was TRANSPOSE that I was talking about.
    Copy and paste from Horizontal to Vertical and vice versa.

    I am trying to copy wherein I would like the rows to freeze and not column.


    I have applied

    Code
    =Sheet2!$G15


    Then when I copy paste, the Column is freezed but not the rows.
    I even tried with


    Code
    =Sheet2!G$15,   and  =Sheet2!$G$15


    But same thing happened.
    What I want is the Row to Freeze so that when I copy paste it becomes


    Code
    =Sheet2!G15
    =Sheet2!H15
    =Sheet2!I15
    =Sheet2!J15
    
    
    etc.

    Is there any formula in excel where the function chooses or selects any of the three numbers 10 or 100 or 1000 randomly and not any other number.
    But only these three numbers should be chosen at random either 10 or 100 or 1000 and every time I reset the chosen number should change.
    For eg if one time it it chooses 10 next time it should be either 100 or 1000

    Re: Match Entry Not Found


    I have changed the data little bit for easy understanding.
    All the alphabets have been changed to Center1, Center2, Center3 etc. and the text data have been changed.


    Since I do not have to create a separate sheet for each Centre I have created a dropdown list in Sheet2


    Here only 2 numbers ie. Number 2 and 3 have different numbers, ie. 70000 and rest have 45000
    If we select Center1 and based on data in Sheet1 if Center1 has either 2 or 3 number it should display 70000 else it should display 45000.

    Same for other Centres if we select any other Centers from drop down list, and if it that center contains either 2 or 3 it should display 70000 else it should display 45000.

    This data of Centers with combination of numbers changes every month and therefore I cannot keep it constant.


    Please let me know if I am clear, else I would try and upload the original file with all the details.

    As of now I am unable to upload the exact file as the size would become larger.


    Reattaching the file with little changes.

    Re: Match Entry Not Found


    Sir the entire worksheet has been changed based on your proposal.
    That is not what I require, The sheet I have attached first, I would like the data to appear that way.

    Please see the attached sheet.


    Sheet 1 contains the data


    Sheet 2 contains the data that displays the text depending on selection of the list in drop down menu.


    If you can see that I have selected a in cell G6 in sheet2, based in this selection the text should appear as Match Found in Cell G9, but it is not being shown as Match Found. (The formula says that if a is selected and if value is 2 or 3 it should display Match Found)


    If you observe in Sheet1 the value of "a" is 2 (In cell C5) and in sheet2 the formula in cell G9, mentions that if "a" is selected and if the value is 2 then it should mention MATCH FOUND.

    I have various sheets like data1, data2 and data3


    In data4 when I select the type of data in cell D4


    I would like all the contents of that particular sheet to be copied in data 4 and duplicates to be removed


    For Eg. if Data1 is selected then from sheet data1 all the data from row 4 I want it to be copied in sheet named data4 to row D6 as shown in example


    Same for all other sheets the data should be copied to RowD6 only, the illustrations that I have given is just an indication as to how I want if other sheets are selected.


    I do not want a VBA code but a formula because the data would change every time you select different sheet from the drop down list


    The columns should also get adjusted


    I hope I am clear

    Re: VLOOKUP in VBA


    Amik I would like to make some more changes in the VBA code you have given.


    The code as it is works very well.


    What I have added is a small message box that displays if we want to display the information with expense 9 with option (Yes or No)
    If we select option Yes then your code would be same without any change.


    If we select option NO then I would like to display the amounts minus Expense 9.


    I think there should be a change in the formula as can see in the VBA code


    Please see attachment

    Please see the attached file wherein I have already made the Vlookup formula and it works well.


    I was wondering if there could be a simpler VBA code or a shorter one for all Vlookup formulas so that I need not write for each month separately.


    The dates I have shown is only till Aug, wherein I have data till March.


    I also get an debug error on the following



    Code
    =IF(VLOOKUP(A7,Apr!$A$8:$C$16,3,0)>0,VLOOKUP(A7,Apr!$A$8:$C$16,3,0),"")