In BASIC, there is a function GOSUB & RETURN. In Excel, is there a equal or identical function?

%3CLINGO-SUB%20id%3D%22lingo-sub-1958989%22%20slang%3D%22en-US%22%3EIn%20BASIC%2C%20there%20is%20a%20function%20GOSUB%20%26amp%3B%20RETURN.%20In%20Excel%2C%20is%20there%20a%20equal%20or%20identical%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958989%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20BASIC%2C%20there%20is%20a%20function%20GOSUB%20%26amp%3B%20RETURN.%20In%20Excel%2C%20is%20there%20a%20equal%20or%20identical%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20routine%20for%20a%20particular%20number.%26nbsp%3B%20I%20want%20to%20go%20to%20where%20the%20routine%20is%20for%20the%20particular%20number%20then%20return%20to%20the%20next%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExamples%3A%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20Col.A%2C%20Row1%2C%20the%20next%20step%20would%20be%20in%20Col.C%2C%20Row%205%2C%20then%20return%20to%20Col.A%2C%20Row%201%20%22...%2Cb%3D75...%22%20or%20Col.A%2C%20Row%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20Col.A%2C%20Row3%2C%20the%20next%20step%20would%20be%20in%20Col.C%2C%20Row%205%2C%20then%20return%20to%20Col.A%2C%20Row3%26nbsp%3B%20%22...%2Cb%3D75...%22%20or%20Col.A%2C%20Row%204%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20Col.A%2C%20Row5%2C%20the%20next%20step%20would%20be%20in%20Col.C%2C%20Row%202%2C%20then%20return%20Col.A%2C%20Row5%20%22...%2Cb%3D75...%22%20or%20Col.A%2C%20Row%206%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BC%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E1%20%3DIFS(b%3D45%2Cgosub%20c5%2Cb%3D75%2Cgosub%20c2%2CTrue%2C%22%22)%26nbsp%3B%20%26nbsp%3B%2045%26nbsp%3B%20%26nbsp%3B%20%3D%22routine%20one%22%3Areturn%3C%2FP%3E%3CP%3E2%20%3DIFS(b%3D45%2Cgosub%20c5%2Cb%3D75%2Cgosub%20c2%2CTrue%2C%22%22)%26nbsp%3B%20%26nbsp%3B%2060%26nbsp%3B%20%26nbsp%3B%20%3D%22routine%20two%22%3Areturn%3C%2FP%3E%3CP%3E3%20%3DIFS(b%3D45%2Cgosub%20c5%2Cb%3D75%2Cgosub%20c2%2CTrue%2C%22%22)%26nbsp%3B%20%26nbsp%3B%2045%26nbsp%3B%20%26nbsp%3B%20%3D%22routine%20three%22%3Areturn%3C%2FP%3E%3CP%3E4%20%3DIFS(b%3D45%2Cgosub%20c5%2Cb%3D75%2Cgosub%20c2%2CTrue%2C%22%22)%26nbsp%3B%20%26nbsp%3B%2081%26nbsp%3B%20%26nbsp%3B%20%3D%22routine%20four%22%3Areturn%3C%2FP%3E%3CP%3E5%20%3DIFS(b%3D45%2Cgosub%20c5%2Cb%3D75%2Cgosub%20c2%2CTrue%2C%22%22)%26nbsp%3B%20%26nbsp%3B%2075%26nbsp%3B%20%26nbsp%3B%20%3D%22routine%20five%22%3Areturn%3C%2FP%3E%3CP%3E6%20%3DIFS(b%3D45%2Cgosub%20c5%2Cb%3D75%2Cgosub%20c2%2CTrue%2C%22%22)%26nbsp%3B%20%26nbsp%3B%2073%26nbsp%3B%20%26nbsp%3B%20%3D%22routine%20six%22%3Areturn%3C%2FP%3E%3CP%3E7%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1958989%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959145%22%20slang%3D%22en-US%22%3ERe%3A%20In%20BASIC%2C%20there%20is%20a%20function%20GOSUB%20%26amp%3B%20RETURN.%20In%20Excel%2C%20is%20there%20a%20equal%20or%20identical%20funct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888892%22%20target%3D%22_blank%22%3E%40denamark1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20best%20things%20you%20can%20do%20if%20you%20have%20programming%20experience%20before%20coming%20to%20Excel%20is%20to%20forget%20said%20programming%20experience.%20It%20really%20can%20interfere%20with%20picking%20up%20Excel.%20I%20say%20that%20as%20someone%20who%20learned%20to%20program%20first%20in%201970%2C%20picking%20up%20first%20BASIC%2C%20then%20APL....APL%20(A%20Programming%20Language)%20being%20powerful%20in%20working%20with%20multi-dimensional%20arrays%2C%20much%20like%20a%20workbook%20with%20not%20only%20multiple%20sheets%20in%203-D%2C%20but%20for%20all%20practical%20purposes%20unlimited%20dimensions%2C%20impossible%20to%20visualize%2C%20yet%20constituting%20addressable%20%22cells%22%20at%20the%20intersections%20of%20all%20those%20dimensions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20get%20it....why%20is%20it%20so%20hard%20to%20do%20what%20was%20so%20easy%20in%20BASIC.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20lucky%20in%20that%20when%20Lotus%201-2-3%20came%20along%20and%20introduced%20the%20power%20of%20computing%20to%20the%20non-programmer%2C%20Lotus%20also%20came%20with%20manuals%20that%20could%20be%20held%20in%20your%20hand%20and%20read%2C%20cover-to-cover.%20Which%20is%20what%20I%20used%20to%20do.%20And%20always%2C%20in%20doing%20that%20anew%2C%20I'd%20come%20across%20a%20given%20function%20and%20say%20to%20myself%2C%20%22Ah%2C%26nbsp%3B%20that's%20how%20to%20do%20xyz%20in%20Lotus%2C%20that's%20an%20easier%20way%20to%20do%20___________%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20part%20of%20that%20is%20getting%20away%20from%20thinking%20BASIC%20routines%20as%20if%20they're%20the%20only%20way%20to%20do%20things.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20of%20which%20is%20my%20invitation%20to%20you%20to%20describe%20in%20words%2C%20or%20with%20an%20example%20that%20does%20NOT%20use%20programming%20terms%20like%20%22gosub%22....what%20it%20is%20that%20you're%20trying%20to%20do.%20I%20have%20the%20sense%20that%20whatever%20it%20is%2C%20it%20will%20be%20quite%20easy%2C%20and%20your%20use%20of%20IFS%20suggests%20to%20me%20that%20you're%20already%20quite%20close.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20an%20application%20in%20mind%3F%20What%20is%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20if%20you%20want%20to%20keep%20digging%20on%20your%20own%2C%20Have%20you%20checked%20out%20Tables%2C%20and%20the%20use%20of%20the%20various%20LOOKUP%20functions%20to%20retrieve%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20always%20VBA%2C%20but%20I%20truly%20think%20Excel's%20built%20in%20functions%20can%20probably%20do%20what%20you're%20seeking....and%20I'd%20love%20to%20help%2C%20but%20I%20need%20to%20get%20what%20those%20various%20cells%20you're%20going%20to%20in%20your%20use%20of%20%22Gosub%22%20are%20going%20to%20do.......%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959494%22%20slang%3D%22en-US%22%3ERe%3A%20In%20BASIC%2C%20there%20is%20a%20function%20GOSUB%20%26amp%3B%20RETURN.%20In%20Excel%2C%20is%20there%20a%20equal%20or%20identical%20funct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888892%22%20target%3D%22_blank%22%3E%40denamark1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20add%20a%20postscript%20to%20what%20I%20wrote%20before.%20I've%20been%20reflecting%20on%20your%20request%20and%20a%20couple%20of%20new%20thoughts%20occurred%20to%20me%3A%3C%2FP%3E%3COL%3E%3CLI%3EYour%20%22RETURN%22%20idea%2C%20from%20Basic%2C%20running%20some%20sub-routine%2C%20returning%20a%20result%2C%20and%20then%20going%20on%20to%20the%20next%20row%20down....that's%20accomplished%20in%20many%20cases%20by%20the%20simple%20act%20of%20copying%20the%20formula%2C%20the%20IFS%20formula%20perhaps%2C%20from%20one%20row%20to%20the%20next.%20%3CSTRONG%3EExcel%20uses%20that%20simple%20act%20of%20copying%20formulas%20down%2C%20row%20after%20row%2C%20to%20do%20the%20same%20thing%2C%20row%20after%20row%2C%20in%20your%20case%3C%2FSTRONG%3E%2C%20IF%20this%20THEN%20go%20to%20sub%20whatever%20and%20deliver%20the%20result%20here.%20It's%20not%20iterative%20in%20the%20same%20way%20as%20in%20Basic%2C%20but%20it%20accomplishes%20much%20the%20same%2C%20going%20on%20down%20the%20column%20or%20sheet.%3C%2FLI%3E%3CLI%3EWhatever%20the%20different%20sub-routines%20are%20that%20you%20have%20in%20mind%2C%20called%20by%20your%20%22gosub%2C%22%20probably%20are%20in%20Excel%20delivered%20in%20the%20form%20of%20functions.%20And%20there%20is%20the%20capability%2C%20if%20you%20don't%20find%20a%20pre-defined%20function%20that%20does%20what%20you%20want%2C%20to%20create%20a%20user-defined%20function.%3C%2FLI%3E%3C%2FOL%3E%3CP%3ESo%2C%20again%2C%20if%20you%20could%20describe%20an%20example%20or%20two%20what%20you're%20looking%20for%20in%20the%20way%20of%20subroutines%2C%20I%20think%20we%20could%20point%20you%20in%20the%20direction%20of%20a%20pre-defined%20Excel%20function%20that%20does%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

In BASIC, there is a function GOSUB & RETURN. In Excel, is there a equal or identical function?

 

I have a routine for a particular number.  I want to go to where the routine is for the particular number then return to the next row.

 

Examples: 

in Col.A, Row1, the next step would be in Col.C, Row 5, then return to Col.A, Row 1 "...,b=75..." or Col.A, Row 2

 

in Col.A, Row3, the next step would be in Col.C, Row 5, then return to Col.A, Row3  "...,b=75..." or Col.A, Row 4

 

in Col.A, Row5, the next step would be in Col.C, Row 2, then return Col.A, Row5 "...,b=75..." or Col.A, Row 6

 

 

                             A                                                 B                     C           

1 =IFS(b=45,gosub c5,b=75,gosub c2,True,"")    45    ="routine one":return

2 =IFS(b=45,gosub c5,b=75,gosub c2,True,"")    60    ="routine two":return

3 =IFS(b=45,gosub c5,b=75,gosub c2,True,"")    45    ="routine three":return

4 =IFS(b=45,gosub c5,b=75,gosub c2,True,"")    81    ="routine four":return

5 =IFS(b=45,gosub c5,b=75,gosub c2,True,"")    75    ="routine five":return

6 =IFS(b=45,gosub c5,b=75,gosub c2,True,"")    73    ="routine six":return

7

3 Replies

@denamark1 

 

One of the best things you can do if you have programming experience before coming to Excel is to forget said programming experience. It really can interfere with picking up Excel. I say that as someone who learned to program first in 1970, picking up first BASIC, then APL....APL (A Programming Language) being powerful in working with multi-dimensional arrays, much like a workbook with not only multiple sheets in 3-D, but for all practical purposes unlimited dimensions, impossible to visualize, yet constituting addressable "cells" at the intersections of all those dimensions.

 

So I get it....why is it so hard to do what was so easy in BASIC.

 

I was lucky in that when Lotus 1-2-3 came along and introduced the power of computing to the non-programmer, Lotus also came with manuals that could be held in your hand and read, cover-to-cover. Which is what I used to do. And always, in doing that anew, I'd come across a given function and say to myself, "Ah,  that's how to do xyz in Lotus, that's an easier way to do ___________"

 

And part of that is getting away from thinking BASIC routines as if they're the only way to do things.

 

All of which is my invitation to you to describe in words, or with an example that does NOT use programming terms like "gosub"....what it is that you're trying to do. I have the sense that whatever it is, it will be quite easy, and your use of IFS suggests to me that you're already quite close.

 

Do you have an application in mind? What is it?

 

Or if you want to keep digging on your own, Have you checked out Tables, and the use of the various LOOKUP functions to retrieve data?

 

There's always VBA, but I truly think Excel's built in functions can probably do what you're seeking....and I'd love to help, but I need to get what those various cells you're going to in your use of "Gosub" are going to do.......

@denamark1 

Let me add a postscript to what I wrote before. I've been reflecting on your request and a couple of new thoughts occurred to me:

  1. Your "RETURN" idea, from Basic, running some sub-routine, returning a result, and then going on to the next row down....that's accomplished in many cases by the simple act of copying the formula, the IFS formula perhaps, from one row to the next. Excel uses that simple act of copying formulas down, row after row, to do the same thing, row after row, in your case, IF this THEN go to sub whatever and deliver the result here. It's not iterative in the same way as in Basic, but it accomplishes much the same, going on down the column or sheet.
  2. Whatever the different sub-routines are that you have in mind, called by your "gosub," probably are in Excel delivered in the form of functions. And there is the capability, if you don't find a pre-defined function that does what you want, to create a user-defined function.

So, again, if you could describe an example or two what you're looking for in the way of subroutines, I think we could point you in the direction of a pre-defined Excel function that does it.

Or, maybe the gosubs are supposed to be cell references to other formulas?