Monthly Sales Spreadsheet Update

[rkeith1885]

Each month I extract sales information from quickbooks but quickbooks does not contain some custom data I would like to see on my sales report. In this case it is region data for specific accounts.

So I have the Account Name which is pulled from Quickbooks in Column E. In my spreadsheet I have added column K which is Region. The Region is not information in Quickbooks so I have to manually update this.

Going forward I would like to be able to add monthly sales to this master spreadsheet and when a previous account is recognized in the new month data the Region info automatically fills in.

Attached is a sample of the spreadsheet I am referencing.

Is this possible?

[#2]

@rkeith1885 

The solution could  be to build a look up table to find region and subregion.  Please find a demo in attached sheet.  it has new tab called regionlookup, where all unique value of column E are listed along with region /sub region. 

the formula in Region and subregion of Sheet1 tab gets the region every time you enter a new account in column E. 

The look up values need to be completed for all accounts. 

i also converted Sheet1 in a table (menu  Home->Format as Table), this will copy down formulae automatically.   

hope this helps 

[#3]

@Kodipadythank you for this help!!! I really appreciate it and I can totally see what you did on my sample spreadsheet. I guess I am just now stuck on copying the equation you used onto my official spreadsheet. I attached it.

Master Spreadsheet is Table1 and Total Customer Database is Table2. Here is the equation I am using in column K of the Master Data tab. You can also see how it is written in the attached spreadsheet.

=IF(VLOOKUP(E2,Table1,2,FALSE)="","",VLOOKUP(E2,Table1,2,FALSE))

I feel like this is an exact copy of your equation taking into consideration the differences of the spreadsheets.

Any further help would be much appreciated .

Thank you, Ryan

[#4]

@Kodipady the equation above is not what I used. That is what you used. Here is my new equation

=IF(VLOOKUP(E2,Table2,6,FALSE)="","",VLOOKUP(E2,Table2,6,FALSE))

Thank you

[#5]

@rkeith1885 

hi Ryan,

please use this formula ( present in check attached file).  

=IF(VLOOKUP(E2,Table2[[#All],[Name]:[Sub Region]],3,FALSE)="","",VLOOKUP(E2,Table2[[#All],[Name]:[Sub Region]],3,FALSE))

The reason you were not getting the intended result was because the Customer name field was not the first column in Table2, rathe it was 4th column. VLOOKUP formula  always uses first column of search array to look up. In my formula, i have selected columns from Name till sub region, so that VLOOKUP gets what is needed to search. 

hope this helps !!

[#6]

@rkeith1885 

As variant you may use the same formula without condition

=VLOOKUP(E2,Table2[[#All],[Name]:[Sub Region]],4,FALSE)

and apply custom General;; format to the column.

[#7]

@KodipadyThank you so much. I really appreciate the time!

[#8]

@Sergei BaklanThank you so much. I really appreciate the help!