• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can one dynamic workbook, containing multiple worksheets, behave as more than one using a KEY?

D

dhouston1000

New Member
Can one dynamic workbook, containing multiple worksheets, behave as more than one using a KEY?
I am developing an event bar tracking workbook. It works as intended. I am looking to allow it to support multiple bars per singular event without copying the event worksheet. The issue is in selecting the inventory. It accesses an inventory tab that toggles X on or off based on the event's required criteria. This is then filtered onto the bartender worksheet. Sometimes, one of the 2 bars may be slightly different from the other in what it presents for sale at the same event. I am limited in using the workbook, as it is intended for use in Excel on Teams on a tablet. (no VBSCRIPT, and I develop at home and can not use scripts linked to buttons.)
The only method I've found is an Office Script that copies and pastes values into certain ranges. (ranges do not delete formulas.) After this script runs, the dynamic construction is removed, and values are locked in. It is the storing of the values. BAR_ID=1, BAR_ID=2, and so on. Can these values be stored in a master table or helper table, and then retrieved when the user or supervisor wants to see whichever bar they choose?
I asked Copilot about creating a giant LET(). As in, LET(this sheet, KEYED to BAR_ID=1, etc.). Then I asked if I could build a worksheet full of formulas, and another worksheet filled with indirect (pointing back to the formulas). Now I wonder if LAMDA could help.

The only method I've found is an Office Script that copies and pastes values.
 
Cross-posted:

Can one workbook, w/ multiple worksheets, behave as more than one using a a KEY?

Can one dynamic workbook, containing multiple worksheets, behave as more than one using a KEY? I am developing an event bar tracking workbook. It works as intended. I am looking to allow it to support multiple bars per singular event without copying the event worksheet. The issue is in...
www.excelforum.com
www.mrexcel.com

Can one dynamic workbook, containing multiple worksheets, behave as more than one using a KEY?

Can one dynamic workbook, containing multiple worksheets, behave as more than one using a KEY? I am developing an event bar tracking workbook. It works as intended. I am looking to allow it to support multiple bars per singular event without copying the event worksheet. The issue is in selecting...
www.mrexcel.com www.mrexcel.com
 

dhouston1000​

You should have read below three times already:
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
K

Thread 'Site Rules - New Users - Please Read'

Hi all,

Welcome to the Chandoo.org Forums.

Posting Rules & Etiquette

The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge.

The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in.

Please take a few minutes to read this list of Rules, Procedures and Etiquette's for posting here at the Chandoo.org Forums.

How to get the Best Results at Chandoo.org
  • Use Relevant words in the Title and in the tag Box, This will aid future searches...
  • Like
 
So, what are you saying is that I can't, or shouldn't, post the same question on different forums?
I ask plenty of people the same question when I am seeking an answer. Why would I view forums differently?
And MOST certainly I would post any solution.
 
This is the same explanation posted on the other forums.

I have an Excel workbook designed to track banquet event bar sales at a country club (There are 3 "static" tabs (Parameters, MEMBERS, PRICES) and 8 additional ones (Set-Up, Staff, Items, Bar(x)_Worksheet, Bag_of_Cash, CC_Charges, Member_Charges, & Tickets) driving this. Each is a separate tab at the moment, so all accounting math can be self-contained. Some events can have more than one bar. I could duplicate the workbook and be done with this; however, I was asking whether I could use a BAR_ID or another unique identifier to allow the workbook to be shared via MS Teams and accessed on a wireless tablet. I have implemented as many drop-downs as possible. This is an attempt to get management to adopt this accounting method instead of the paper sheet we currently use. I have already encountered all of the hindrances and limitations between the flavors of Excel and licensing. The worksheet functions, I just want to select bar_1, 2, 3, etc., and access their start/end inventory and payment types. Once I accomplish this, I will be confident in presenting it to management. At the moment, the sheet of paper wins because they can dump it to accounting, and they have to figure everything out.

I ran out of Copilot credits until next month, which is why I am even asking. Since I was reprimanded for asking the same question on different forums, I'm reluctant to continue asking. Really, I just want to know if this is possible.
 
Did You reread that link I sent?
... did You notice: For the best/fastest results... a sample Excel file.
With that, others could have much better image
- what do You have?
- what do You need?
 
A pdf file is pretty useless as it cannot be manipulated. How about an Excel file? And as been explained earlier, we don't need the whole file, but a sample of 10-20 records and a mock up of what your expected results are. I've looked over the explanations you have provided in all three forums and it appears that there is a lack of clarity on your part. Clearer explanation with relating to your sample file is necessary.
 
That link is to the Excel file. pCloud renders a *.pdf preview. The download button correctly links to the actual Excel file.

What I'm asking is:
Is there a way to avoid duplicating worksheets and/or workbooks when a single invoiceable banquet event has two or more bars?

I could certainly provide multiple copies of the same workbook with the file names Event-XYZ-Bar A.xlsx and Event-XYZ-Bar-B.xlsx to each coworker. And then include a third file to summarize the singular event's sales. I was hoping one workbook would suffice for each event.
 
Last edited:
You are not making it easy to help where you have been asked to attach a sample file. Good Luck. I'm out. Not willing to chase your data.
 
Your link would not resolve for me, but it's not the file we asked for. That file would be an actual Excel file, not a picture of it.

Sorry, but this isn't getting us anywhere close to being able to help you.

1773644569490.png
 
Well, thank you, @p45cal.
I am seeking a way to avoid duplicating tabs in the Excel file (i.e., additional tabs to support multiple bars). And duplication of the Excel file (for multiple bars).
This is why I thought a unique ID would accomplish the differentiation.
 
I'm still a long way from clear about this and how it's supposed to work. I see that you have a BAR_1_Worksheet tab - is this the worksheet that you are referring to?

Could you please explain the following so that we can be clear about your intentions?

1. You talk about not wanting to duplicate the sheet within the main workbook - what is the reason for this?
2. Are you trying to have the BAR worksheets in separate workbooks, but linking back to the MAIN workbook with formulae (drawing on source data)?
3. If so, are you simultaneously wanting that input to be reflected live in the MAIN workbook?
4. You mentioned tablets: will these satellite workbooks have to be completely independent of the MAIN workbook? If so, are you looking for a way of somehow integrating them back into the MAIN workbook after the event?

Answers to the above will help to clarify what you are actually trying to do, or they will show that I have no handle at all on what that is - either way, it will help.
 
1.) It starts with event billing. CASH or HOST, which determines who pays the taxes. This determines the pricing. Then, certain bar types do not require all items. For example, a mimosa bar would not need any items other than sparkling wine, etc. All of this is indicated on the Set-Up tab.

2.)The Items tab uses the Set-Up choices to filter the inventory (Items tab) into a helper column.

3.) This helper column is then used by the Bar_Worksheet drop-downs (Beer & Liquor) and the LOOKUP (wine) to represent what was taken from the liquor room and made available for sale at the event pop-up bar.

Some events have more than one bar; however, one bar may have Prince Brut champagne, and the other may have Wycliff. Both products qualify and may be chosen by the bartender for sale. Or bartender 1 may get the last of a particular product, and the 2nd has to select a comparable alternative. The possibility for the bars to be slightly different exists. Or a wedding may have a full bar inside, and outside, it's beer only.

4.) Payment methods include Cash, Member charges, credit card charges. and tickets. If it is a HOST event, payment methods are not necessary OR may include tickets.

All logic points to a CASH bar. Any deviations in IF, IFS, etc. are for HOST.
Since cash may require any or all of the four payment methods, I would be duplicating Bar_worksheet, cash, member charges, credit card charges. and tickets - 5 tabs. This is quite doable, but cumbersome at 10 tabs. The most bars any event has had since I've worked here has been a wedding with three (15 tabs). Thus, I thought I could use BAR_ID to reuse the same worksheets (without duplicating them), keep the event's sales in a single workbook, and use the Summary tab as a dashboard for any billing or auditing questions.

This is a project I took upon myself to pursue. If Excel is not the answer, it's all good. I am not being paid to come up with a solution. The sheet of paper we are handed at the beginning of each event is what is working, and no one, but me, is seeking to automate any of this. Before I present it to management, I was hoping I could include that it accommodates multiple bars per event.
 
Thanks - I am beginning to see how this works.

So, let me see if I can now summarise your requirements: ideally, you'd like one bar worksheet that has ONE set of source data (cash, member charges, credit card charges and tickets) and then a bar worksheet template that can be made to work with those source data sheets without the need to duplicate them. Do I now have this right? I am sorry it's taking so long, but you have (necessarily) presented us with a fairly complex workbook that you have built and understand, but that we have no way of understanding without a clear explanation thereof.

I shall have another look at your workbook shortly, but here are my thoughts on how I might achieve this:

1. You might need an extra worksheet (like a middle man) where you define what constitutes each type of bar (in summary) and give each a code. This would be best in some sort of tabular format.
2. On the template bar sheet, you have a drop-down code to choose the type of bar.
3. The formulae that populate the bar sheet then reference the 'middle man' sheet to find out which aspects of the four source sheets they need to be referencing.
4. This template can then be copied multiple times and used in the same workbook for different types of bar.

Am I getting any closer in my understanding of the problem that needs a solution?
 
Yes. I toyed with the idea of a bar_type selection grid or table. I choose to just flip an X on or off with IFS, based on CASH/HOST. Then, as stated, certain bars do not require each item. A wine & beer-only bar would not need liquor, etc. This further flips X's on and off, depending on the criteria. A FILTER then displays each "Item" with an X, indicating it is available for selection via a dropdown on the Bar_x_worksheet in a helper column. There is a Wine Override, but that is a unrelated topic.

Once the event starts, some items may be popular and need to be restocked. Some may get spilled. All of the bar's activity during the event is recorded and calculated on the Bar_worksheet. This determines the bar's inventory depletion. Based on this, sales are calculated, including taxes and gratuity at their respective percentages. Once these values are determined and subtracted, a net total is calculated and must be reconciled with each payment method. Which may include any combination of counting money and/or tickets and entering member and credit card charges. Indicating each in its designated tab. Note: HOST bars just have to calculate the sales, and that Total is what is invoiced to the client.

I was hoping to use the existing worksheets (without duplicating) and designate, say, the ballroom bar as "1", the entry bar as "2", and so on, if multiple bars were required. Each table has a Bar_ID column. I color-coded certain cells, hoping to indicate their roles within the workbook. Yellow=manual entry, peach=dropdown, light blue=yes/no toggle, grey diagonal lines=LEAVE ALONE CALCULATIONS UNDERNEATH. This is to visually steer the user. I understand that Excel does not have the ability to control users from copying and pasting over formulas, even with data validation.

At the end of the event, the supervisor would view the Summary tab (dashboard) and verify, print, or save as PDF and send to accounting.
 
One step forward and three back ... !!!

So, let's try again. Are you wanting to use the same worksheet to collate ALL of the bar data? Or is it one sheet per staff member? Or is it one sheet per bar?

I can see how you have set up the sheet, but I still don't fully understand how you anticipate it be used.

Let's say you have two bars at an event: full and wine & spirits (I'm English!) only.
Let's also say that there will be two staff running each bar: Freddy & Fiona on the first, and Winston & Sarah on the second.
What am I going to see? Will there be:
1. One worksheet per bar that can be shared by the members of that bar's staff?
2. One worksheet with entries from both bars and shared by all bar staff?
3. Something else?

I think you need to focus on the basic usage first and forget about the minutiae at this stage, because for an outsider, it's too much, and 20 posts in, we still haven't established the basic premise here (at least, not for me, and I do have knowledge of the hospitality trade, with a daughter who is a professional bar manager and mixologist and having 'lived' that life for several years with her father, a publican).

Forget for now about calculations and what might happen after the event - focus JUST on the set-up ready for the event.

In all honestly, if it were me, I'd be starting again from scratch. I have built systems like this (in a different context), and this one is, to my mind, unnecessarily complicated as it stands.
 
You must log in or register to reply here.
Back
Top