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