Aug 07 2019 01:59 PM
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?
Aug 07 2019 08:04 PM
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
Aug 08 2019 04:52 PM
@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
Aug 08 2019 04:54 PM
Aug 08 2019 09:32 PM
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 !!
Aug 09 2019 02:38 AM
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.
Aug 09 2019 05:13 PM
@KodipadyThank you so much. I really appreciate the time!
Aug 09 2019 05:13 PM
@Sergei BaklanThank you so much. I really appreciate the help!