• 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:
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.
 
You must log in or register to reply here.
Back
Top