12-05-2020 07:03 AM
12-05-2020 07:03 AM
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.
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
12-05-2020 09:02 AM
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.......
12-05-2020 01:18 PM
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:
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.
12-05-2020 03:48 PM