how to lookup one value to set a range for another lookup

[jgreene11]

How do I write a lookup that will search for the revenue center then drop down to find food then over to pull the value from a meal period, on the reference sheet there are multiple revenue centers and therefore multiple cells with the word food in them?

Net Sales For Revenue Center HTREST
	Breakfast	Lunch	Dinner	Late Night	Grand Ttl
FOOD	600​	282.4​	2,378.30​	0​	3,260.70​
N/A BEV	0​	45.25​	71.25​	0​	116.5​
LIQUOR	0​	0​	384.25​	0​	384.25​
BEER	0​	0​	112​	0​	112​
	0​	0​	0​	0​	0​
	0​	0​	0​	0​	0​
WINE	0​	0​	250​	0​	250​

 

[jgreene11]

@awoohaw has offered some excellent advice. I would also suggest providing some idea about what you would like the final result to look like? For example, are you trying to construct a summary table where all of the "Food" entries for a particular meal type are summed across all Revenue Centers, or just one? Based on your comment about this being a daily report, I like the idea of using Power Query, but am not sure your Excel version supports it (which is why that information is critical).

And since this represents a daily report, are you interested in capturing the date and assigning it to these records so that you can evaluate performance over time? Where would the date of the daily report be found?

The daily report is an excel document that is generated by the point of sale system. I then just rename the file and set it into a folder on my computer, so all of the reports would be titles as Rev 10-29-23, Rev 10-30-23, Rev 10-31-23 etc.

It appears as if all of the revenue centers are like the copy in the first posting and it food is 4 rows below revenue center, N/A Bev is 5 rows below revenue center, Liquor is 6 rows below revenue center and the meal periods all line up as 1 column over for breakfast, 2 columns over for lunch, 3 columns over for dinner.

I have tried setting up the XL2BB, but the work computer keeps coming back with not allowed in protected mode.

Yes the design is that it looks at the performance of each of the attributes of sale across all of the revenue centers per meal period as well as per day, and then totaled up per week, so that in the end it is showing the percentage revenue during the meal period per revenue center, the revenue per cover, as well as the revenue per occupancy.

 

[awoohaw]

Then it seems like your scenario can be very easily managed using Power Query. Please watch the video in the playlist I linked in my earlier post.

 

[KRice]

The Power Query example below might suit your needs. You mentioned receiving daily reports in an Excel file format and that there is interest in examining revenue over time and across several different factors (meal type, revenue center, etc.). You may be best served by creating and then maintaining on a daily basis a growing cumulative flat table that contains the relevant information. By "flat", I mean there are five columns in the cumulative table: Category (Food, Liquor, etc.), Center (the name of the Revenue Center), Date (of the daily report), Meal Type, and Revenue. Then the performance analysis can be readily done on an Excel worksheet with some relatively simple sums of data extracted from the cumulative table with FILTER functions (subject to various conditions that you'll define). Alternatively, you could extend the Power Query code to have PQ perform the analysis or use Pivot Tables.

The approach outlined here assumes that:

1. You place all of the daily report workbooks in the same folder and that this RevenueSummary workbook (containing everything else I'm describing here) is also in the same folder.
2. You maintain a daily file naming convention of "Rev mm-dd-yy.xlsx" (personally, I prefer a system like Rev_yyyymmdd.xlsx to facilitate sorting, but the method here is based on the name system you've described).
3. There is only one worksheet in each daily report workbook.
4. The column structure of each daily report workbook is the same.

If these assumptions are not correct, then the code will need to be modified.

We establish three tables on a worksheet called "ProcessDailyReport". One table is called "FileList" and it contains a list of file names in the target folder that begin with "Rev" and have an extension of ".xlsx". You will need to refresh this table by selecting any cell in the FileList table and then click Data on the top level menu, then click the down arrow for Refresh All and choose Refresh...and you should see the table update will all relevant filenames. Another table called "Parameters" includes a drop down selector that reads the FileList table to give the user a convenient way to specify which daily report to process. This table extracts the file path information where this RevenueSummary.xlsx file is located (and then all of the daily report files should be found there too...see assumption 1 above). Once a daily file has been chosen, click on Data>Refresh All to run the main script, resulting in a flat table for the daily report. The script extracts text from the file name and converts it to a date, and this date is assigned to each of the records.

At this point, I'm not sure what you might want to do. I would probably scroll through the table and confirm that it looks okay, and then manually copy the daily flat table and paste it onto the end of the cumulative table...but you could have PQ perform this append operation automatically with the addition of a couple more steps. If you opt for the manual approach, after confirming the daily table is okay, be sure to select any cell in the table, then Ctrl-a to select the entire table, then Ctrl-c to copy the table to the clipboad, then navigate to the Cumulative worksheet and use the shortcut Ctrl-End to jump to the end of your worksheet, select the cell in column A just below the bottom of your table and apply the shortcut Ctrl-v to paste the newly processed daily data into the Cumulative table. These manual steps collectively take only a few seconds, but because of this manual intervention, you will avoid writing bad data into your Cumulative table, as could happen if the format of the daily report changes and no one tells you. Still, if bad data are written to the Cumulative table, simply delete all of the records for that date and debug the issue so that the entire day's report processes correctly, and then insert the correct data.

Then you can use any of several methods (e.g. expanded PQ script, Pivot Table, Excel formulas) to extract the information from the Cumulative table for your performance analysis. I'm not sure what you have in mind for the analysis, but feel free to post back for clarification on anything here.
Here is the Parameters table:

RevenueSummary.xlsx
	A	B
1	Choose File to Process
2	Parameter	Value
3	Path	C:\Users\kirkr\Documents\@Home\Hobbies_Misc\ExcelStuff\Revenue\
4	File	Rev 10-29-23.xlsx
ProcessDailyReport

Cell Formulas
Range	Formula
B3	B3	=LEFT(CELL("filename"),SEARCH("\[",CELL("filename")))

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=INDIRECT("FileList")

The FileList table:

RevenueSummary.xlsx
	J
1	FileList
2	Name
3	Rev 10-29-23.xlsx
4	Rev 10-30-23.xlsx
ProcessDailyReport

A small sample of the flattened daily table:

RevenueSummary.xlsx
	A	B	C	D	E
6	Flattened Daily Table
7	Category	Center	Date	Meal Type	Revenue
8	FOOD	HTREST	10/29/2023	Breakfast	$ 600.00
9	FOOD	HTREST	10/29/2023	Lunch	$ 282.40
10	FOOD	HTREST	10/29/2023	Dinner	$2,378.30
11	FOOD	HTREST	10/29/2023	Late Night	$ -
12	FOOD	HTREST	10/29/2023	Grand Ttl	$2,378.30
13	N/A BEV	HTREST	10/29/2023	Breakfast	$ -
14	N/A BEV	HTREST	10/29/2023	Lunch	$ 45.25
15	N/A BEV	HTREST	10/29/2023	Dinner	$ 71.25
16	N/A BEV	HTREST	10/29/2023	Late Night	$ -
17	N/A BEV	HTREST	10/29/2023	Grand Ttl	$ 71.25
18	LIQUOR	HTREST	10/29/2023	Breakfast	$ -
19	LIQUOR	HTREST	10/29/2023	Lunch	$ -
20	LIQUOR	HTREST	10/29/2023	Dinner	$ 384.25
21	LIQUOR	HTREST	10/29/2023	Late Night	$ -
22	LIQUOR	HTREST	10/29/2023	Grand Ttl	$ 384.25
23	BEER	HTREST	10/29/2023	Breakfast	$ -
24	BEER	HTREST	10/29/2023	Lunch	$ -
ProcessDailyReport

A small sample of the Cumulative table on worksheet "Cumulative Daily Reports" (I've hidden many rows):

RevenueSummary.xlsx
	A	B	C	D	E
1	Cumulative Daily Reports
2
3	Center	Category	Date	Meal Type	Revenue
4	FOOD	HTREST	10/29/2023	Breakfast	$ 600.00
5	FOOD	HTREST	10/29/2023	Lunch	$ 282.40
6	FOOD	HTREST	10/29/2023	Dinner	$ 2,378.30
7	FOOD	HTREST	10/29/2023	Late Night	$ -
8	FOOD	HTREST	10/29/2023	Grand Ttl	$ 2,378.30
9	N/A BEV	HTREST	10/29/2023	Breakfast	$ -
10	N/A BEV	HTREST	10/29/2023	Lunch	$ 45.25
11	N/A BEV	HTREST	10/29/2023	Dinner	$ 71.25
12	N/A BEV	HTREST	10/29/2023	Late Night	$ -
54	FOOD	HTREST	10/30/2023	Breakfast	$ 644.00
55	FOOD	HTREST	10/30/2023	Lunch	$ 326.40
56	FOOD	HTREST	10/30/2023	Dinner	$ 2,420.30
57	FOOD	HTREST	10/30/2023	Late Night	$ 1.00
58	FOOD	HTREST	10/30/2023	Grand Ttl	$ 2,390.30
59	N/A BEV	HTREST	10/30/2023	Breakfast	$ 6.00
60	N/A BEV	HTREST	10/30/2023	Lunch	$ 52.25
61	N/A BEV	HTREST	10/30/2023	Dinner	$ 101.25
Cumulative

The M code called "DailyRpt" that transforms the daily report into a flat table:

let
/*  
Retrieves the file path and file name from the parameter table to create an absolute address to the file of interest, and then
stages that file as the "Source" for subsequent referencing in the DailyRpt query. This effectively creates a staging area for bringing external files into Power Query, and since the file is obtained as a result of a query, PQ will then treat the source file as an internal one, thus avoiding an error if a Merge Query operation is attempted.
*/
    #"Source" = Excel.Workbook(File.Contents(#"fnGetParameter"("Path") & #"fnGetParameter"("File")), null, true),
    DailyReport_Sheet = Source{[Item="DailyReport",Kind="Sheet"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(DailyReport_Sheet,{{"Column1", "Category"}, {"Column2", "Breakfast"}, {"Column3", "Lunch"}, {"Column4", "Dinner"}, {"Column5", "Late Night"}, {"Column6", "Grand Ttl"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Category] <> null)),
    TagRevenueCenters = Table.AddColumn(#"Filtered Rows", "Center", each if Text.Contains([Category], "Revenue Center") then Text.AfterDelimiter([Category],"Revenue Center ") else null),
    #"Filled Down" = Table.FillDown(TagRevenueCenters,{"Center"}),
    #"Filtered Rows2" = Table.SelectRows(#"Filled Down", each not Text.Contains([Category], "Revenue Center")),
    AddDate = Table.AddColumn(#"Filtered Rows2", "Date", each Text.BetweenDelimiters(#"fnGetParameter"("File"),"Rev ",".xlsx")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(AddDate, {"Center", "Category", "Date"}, "Meal Type", "Revenue"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Revenue", Currency.Type}, {"Date", type date}})
in
    #"Changed Type"

The M code for the FileList query:

let
/* 
Builds a list of files in the specified folder (defined by Path in the parameter table) that have the .txt extension .
The list can then be used with Data > Validation so that the user can select the file of interest for processing.
*/
    Source = Folder.Files(fnGetParameter("Path")),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Rev ") and [Extension] = ".xlsx"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"})
in
    #"Removed Other Columns"

The M code for the function "fnGetParameter" used to pull the selected file into Power Query:

let
    Source = (ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value
in
    Source

Setting this up initially might be a little tricky, so here is a link to the sample file containing everything described above:

RevenueSummary.xlsx

Shared with Dropbox

www.dropbox.com

...and here is a link to one of the daily report workbooks used:

Rev 10-29-23.xlsx

Shared with Dropbox

www.dropbox.com

 

[awoohaw]

@KRice , that is a lot of work. How much time did it take you to do all of that? (Excluding the copy and paste of images into your post.)
I bet all that work was done in not very much time.

Kudos!

 

[KRice]

Thanks. I worked on it "on and off", but its about 1-2 hours total effort. I probably spent as much time writing up a description as I did performing table cleanup on the provided source table and adapting some M code that I've used for other purposes.