SharePoint data not transferring to Table in Excel Completely

  • 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.

  • 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'.