Posts by Vonsteiner

    Re: SharePoint data not transferring to Table in Excel Completely

    Okay, so when I filter every column by [Blanks] then delete it will populate all the data correctly. So now if no one has an easier way to accomplish the above does anyone know a way to delete all the [Blanks] within the workbook/worksheet? All data pasted into the repository is pasted as 'values'.

    I have a workbook that is pulling data into a table (for power query use) based on a drop-down value from a repository workbook in SharePoint. The table populates correctly except that all the values do not seem to be pulled over even though they are in the SharePoint workbook.

    The workbook in SharePoint has a separate worksheet for each month of the year and a summary worksheet that counts the total number of rows with data for each month. Each month worksheet has column headers from column A to AB. See below.

    Repository workbook in SharePoint:

    Month Totals worksheet-- the formula in column B is IF(Jan!A2="",0,OFFSET(ROWJ,COUNTA(Jan!$A:$A)-2,0)) for each month
    A B ROWJ is the range name I gave cell A1 for the Jan worksheet. Each worksheet has its own name range in cell A1
    Month Total
    January 4213 Each month tab has the same headers
    February 5475
    March 4824
    April 4753
    May 0
    June 0
    July 0
    August 0
    September 0
    October 0
    November 0
    December 0

    Each month tab has the same headers and will range from around 4,000 rows to 7,000 rows.

    Starting in Column A
    Row, Month, Client, Loan, LSCN, Work Order, Supplier Number, Supplier Name, City, State, etc...all the way to column AB

    The only formula in the month tabs is in the row column and it is starting in cell A2:

    =IF($B2<>"",1,""), then in cells A3 and below--=IF($B3<>"",$A2+1,"") this gives the total number of rows in the summary sheet explained above.
    All the data in columns B-AB is copy/pasted in manually.

    The problem I am having is when I try to pull this data into another workbook, we'll call it HELP. HELP has a current month drop-down where the user can select whatever month they are looking for data from in cell D1. The formula the other cells use to retrieve information is as follows:

    Cell B3+--=IF(OR($D$1="Select Month",A3=""),"",$D$1)
    For C-AB--=IF(OR($D$1="Select Month",A3=""),"",IF($D$1="January",'[Preservation Repository.xlsx]Jan'!C2,IF($D$1="February",'[Preservation Repository.xlsx]Feb'!C2,IF($D$1="March",'[Preservation Repository.xlsx]Mar'!C2,IF($D$1="April",'[Preservation Repository.xlsx]Apr'!C2,IF($D$1="May",'[Preservation Repository.xlsx]May'!C2,IF($D$1="June",'[Preservation Repository.xlsx]June'!C2,IF($D$1="July",'[Preservation Repository.xlsx]July'!C2,IF($D$1="August",'[Preservation Repository.xlsx]Aug'!C2,IF($D$1="September",'[Preservation Repository.xlsx]Sept'!C2,IF($D$1="October",'[Preservation Repository.xlsx]Oct'!C2,IF($D$1="November",'[Preservation Repository.xlsx]Nov'!C2,IF($D$1="December",'[Preservation Repository.xlsx]Dec'!C2)))))))))))))

    The above formula changes columns as it goes across. I am sure there is an easier way to do this but I am not sure what it is.
    So the second formula fills correctly within HELP from columns C-N (it fills in a 0 if the data cell retrieving from is blank).
    If the column O (we'll call it ROOTS) data is blank in the repository the formula instead of inputting a 0 it leaves the ROOTS cell blank.
    IF ROOTS is blank then columns Q and S-AB are blank. P and R fill in correctly.

    I do not know why it does this. It does this for every month when selected. It will alter the data depending on the month just fine except for the above mentioned problem.
    I would greatly appreciate any help anyone would be able to provide with this issue.
    Thank you.


    I have the below code within the worksheet of my workbook. It allows users to select more than one option from a drop-down menu within one cell. The code works excellent for that, but when I protect the sheet the multiple selection ability is removed and I am not sure why.

    Does anyone know how I can protect the worksheet and still have the multiple selection ability work? Any and all help would be greatly appreciated.


    I am trying to hide a variable number of rows in 8 different worksheets based on a name selected from a drop down menu in a different worksheet. Each time a new name is selected in the drop down all the rows need to be unhidden then re-hidden based on the new name.

    In sheet 1 I have a drop down with roughly 200 names. Each time one is selected I need all the rows NOT associated with this name to become hidden in Sheets 4-12 and only show the rows that include the selected name. If possible I would then like to password protect sheets 4-12 so they cannot be edited or manipulated in any way.

    Here is a synopsis of my workbook:
    Sheet 1 is the dashboard and contains results that have been compiled in a different worksheet (Sheet 3)
    Sheet 2 is very hidden and contains the list of names that appear in the drop down and a number associated to them (some names have multiple numbers and some only have 1)
    Sheet 3 is very hidden and contains the compiled data from sheets 4-11 based on name
    Sheets 4-12 have the data that is being compiled into sheet 3 and shown on sheet 1. these sheets currently have just the number associated with each name but I can add a column for the name if needed to make this work.
    Sheets 4-12 can have from 10-1500 rows of data depending on the month

    When presenting the data to the specific name selected in the drop down in sheet 1 I need only the data (rows) within sheets 4-12 that corresponds to this name shown and all the other rows hidden from view.

    So far this is the code I have to unhide all the rows. I am unsure of how to call each case in order for the code to hide the necessary rows.

    Any help would be greatly appreciated.