+ Reply to Thread
Results 1 to 7 of 7

Single Button Toggle Switch To Switch Between Columns

  1. #1
    Registered User
    Join Date
    06-15-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    34

    Single Button Toggle Switch To Switch Between Columns

    Currently looking to make a toggle button to show our Inventory On Hand which is the inventory we currently have minus our Open Orders (This is represented as On Hand in Column E on Worksheet 2.) and True Sales. (This is represented as True Sales in Worksheet 2 Column I.) [/I] These two columns are linked to Worksheet 1 Column A (Inventory On Hand) and Column B [I](True Sales) via a Vlookup (I have not provided our item codes represented in the vlookup but they will technically be in column A.. I want to be able to press a button and it changes Column A on Worksheet 1 to Total inventory (Column H on worksheet 2) and Column B on Worksheet 1 to January Column A on worksheet 2). Then make it so clicking it again changes it back.

    The purpose of this will it can change all of our projections based on what we sell and have sold, so at the end of the month if we haven't shipped out all of our open orders we can see what were looking like for next month.

    If I am being 100% honest I do not mind trying to work it out myself I just need a little help and a push in the right direction on how to swap between different formulas in a column.

    If more information is needed feel free to ask me.

    Thank you,
    Attached Files Attached Files
    Last edited by Zaerick; 01-27-2022 at 10:35 AM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,956

    Re: Single Button Toggle Switch To Switch Between Columns

    I am only loosely grasping what you are trying to do, but since you mentioned looking up things via VLOOKUP then use something like =VLOOKUP(somevalue, somelookup range, Offset, False) where Offset is determined by your toggle it could be 1 or 8.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Single Button Toggle Switch To Switch Between Columns

    Only suggestion. Add Two more columns In Sheet1 to get required data with vlookup functions.
    If not upload file with lookup functions.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    06-15-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    34

    Re: Single Button Toggle Switch To Switch Between Columns

    Quote Originally Posted by dflak View Post
    I am only loosely grasping what you are trying to do, but since you mentioned looking up things via VLOOKUP then use something like =VLOOKUP(somevalue, somelookup range, Offset, False) where Offset is determined by your toggle it could be 1 or 8.
    Trying to make it so the Vlookups on Sheet 1 change their column lookup with the click of a button. Not sure if that is even possible. I guess I can physically record a macro and do it that way if I must since it will always be the same two columns switching back and forth.
    Last edited by Zaerick; 01-28-2022 at 09:18 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    492

    Re: Single Button Toggle Switch To Switch Between Columns

    Hi

    Another way might be to just split your view into 2 Windows and compare your sheets directly side-by-side.

    In the attached workbook, I added a toggle button on sheet [Forecasting].

    When you click this button, a new second window is created for the file. This allows you to view 2 different sheets at the same time, side-by-side. You can even synchronise scrolling between these visible windows.

    I included vba commands to hide some columns on the [Inventory Report] sheet when you are doing this side-by-side comparison.
    When you click the toggle button again, it cancels all the temporary hidden colums on the [Inventory Report] sheet and then switches you back to your one-window view.

    So, no extra columns or formulas needed, just compare side-by-side.

    Hopefully this will help solve your issue.

    zeddy
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    492

    Re: Single Button Toggle Switch To Switch Between Columns

    Hi Zaerick

    Just in case you didn't like my previous posted suggestion..

    Instead of using VBA as per my previous post, perhaps you could consider using a pair of Option buttons as an alternative to a Toggle button.
    So no vba required.

    The attached file shows an example.
    Clicking an Option button puts a value into a specified linked-cell (in the attached example file, the linked-cell is cell [K1] .

    You can then use the value of this linked-cell in your Inventory-formulas e.g. like this..
    =If(option linked value =1, use this formula, otherwise use this formula)

    I haven't put any such formulas in, you should be able to do that.
    Note: You can also use the linked-cell current value in Conditional-Formatting-rules, as shown in the column A heading-cells on the [Forecasting] sheet.

    zeddy
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,918

    Re: Single Button Toggle Switch To Switch Between Columns

    You can use radio buttons or a checkbox. In the sample file I used radio buttons.
    Named ranges created:
    January ='ws 2- -Inventory Report'!$A$3:$A$169
    OnHand ='ws 2- -Inventory Report'!$E$3:$E$169
    Option ='ws1 - Forecasting'!$D$1
    TotalInv ='ws 2- -Inventory Report'!$H$3:$H$169
    TrueSales ='ws 2- -Inventory Report'!$I$3:$I$169


    typical formula in cells (e.g. A2):
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating a Toggle Switch
    By zyzzyva57 in forum Excel General
    Replies: 6
    Last Post: 12-08-2018, 04:18 PM
  2. [SOLVED] on/ff toggle switch
    By Kausch in forum Excel General
    Replies: 4
    Last Post: 03-23-2018, 12:28 PM
  3. [SOLVED] toggle button to switch data between sheets and back
    By smsmworld in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2017, 09:15 AM
  4. Toggle Button that will switch the cell data between gallons and barrels
    By Kadrak in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2014, 05:42 PM
  5. Linking a cell to a toggle switch
    By Brisbane Rob in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2005, 07:41 PM
  6. [SOLVED] Toggle on/off switch for x-y graph
    By Brad in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-18-2005, 11:05 PM
  7. how to quickly toggle or switch to next worksheet
    By Kwong Yau Leung in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-09-2005, 06:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1