Just discovered this excellent series. I do not have a Mac but these have been so useful on my iPad & iPhone. This particular function has been a real eye-opener. I particularly like the way the items are presented, building from the simple through to the complex. Top marks.
Thank you so much for the excellent tutorials. I grudgingly moved to a Mac a year or so ago and have suffered serious Excel withdrawal symptoms ever since, not realizing that Numbers itself has quite a bit of the same functionality, with a large selection of functions and the ability to use AppleScript with it.
Thanks for offering support at your website. After hours of work, I have finally resolved all my current issues. In case it should help others, on the matter of my simple issue I was making a basic mistake and trying to use “Indirect” to point directly to a cell. The much more complex issue of the error message saying the cell cannot reference itself was because I was putting the function into one of the HEADER columns. It seems like Numbers treats them all as one entity. It was working perfectly in a test table but the column was not set to HEADER. As soon as I did so, it failed. Who knew? I’ve been using quite complex Numbers spreadsheets for years but this issue has never reared its ugly head, before. Thanks for all your good advice👍.
Wow. Finally, got a handle on this. It’s sooo powerful. I’ve got it filtering one sheet, following a filter on another - amazing. Thanks for teaching me about this incredibly useful function. You are the boss.
Excellent video Gary Thank you so much! I am really starting to like numbers more and more over excel anymore! I just don’t need all that power and rather now the nicer look. It’s a very worth while change
I wish he'd been around when I was working for the Center in Los Angeles. Trying to do it with Excel Worksheets didn't work. It's supposed to be this powerful program but I barely knew how to use it. When I worked for the same Center in New York City, I used to have to sit next to the box in which people put their "weapons" while they were at the Center. You weren't allowed to carry anything on their grounds that you could use as a weapon.
I find I can create an address using match for both rows and columns, where each of the two match functions uses indirect on the string of table name and column or row references. To complete the address string you also include the other table’s name again, so I do this by reference to a cell where I have entered a table’s name. Finally the whole thing is wrapped in a third indirect statement. This allows me to flexibly combine in one table, data from as many other tables as I require.
Hey Gary! You are my numbers hero! Is there a way to use the indirect function across all/future sheets? I have spreadsheets separated by weeks and I continue to add new sheets. I am looking to get an average of specific cells across all and future sheets. Is this possible using indirect?
Hello Gray, can you make a video on how to make school marksheet in numbers. it will be very very grateful for teachers those who are using Mac. Thank You
Appreciate your explanations. On this one, why don't we Hit "equals" than click on the exact box that contains the .60? Why is the "indirect" necessary?
Hello, Very nice videos, I didn't understand how you can mix indirect and drop menu and IF function - Let me explain : I want to set a function for my office like if a patient (NAME) has a pathology then he would receive a different treatment depending on the pathology : Exemple patient A has pathology X that has 3 different treatments TTT 1 TTT2 TTT 3 - Patient B has pathology Z with treatments TTT4 TTT5 and TTT 6. I created a list with all the pathologies and different lisst of treatments per pathology - how can I create a function with drop menu like patient name -next column choose between pathologies - next column choose between treatments which should be different from the pathology. Thank you
Hi, nice info you gave us. But I had tried to change the 60 with a cell and I have alway an error. Where I'm get wrong? INDIRECT(Models::$A$1&"::"&Vdx::$A$1&" 60"). I have 4 tables with the model where I choose from....I have a value Voltage and the 60 is the time (minutes) but I need bring it back from another cell....and I'm failing every time.... how can I substitute the 60 with a reference cell? Thanks Gary
thank you, Gary, please help me in applying black scholas formula of option calculation..secondly in excel we use solver function, how the same can be used in numbers..
Sorry, not familiar with "black scholas formula" and the Solver seems to be an add-on to Excel that performs operations, not a function. If you like these things in Excel, then why not just stick with using them in Excel. The apps are different and Excel will always do some thing Numbers doesn't, and Numbers will always do things Excel doesn't.
Don’t get me wrong excel is still the KING but I sure am liking numbers simplicity and curb appeal even if I’m now the only one who looks...😂 it’s plenty strong enough for me
Thanks Gary. I am having real trouble trying to use the indirect method to determine the total amount on our churches tithing numbers sheet when some money goes to the building fund and some to general and some to missions and some to youth. How would I calculate that formula? I am really impressed with your videos but this one seems to give me grief.
You can do it any number of ways. Not sure of your exact situation or why you would need INDIRECT to do that at all. Just a simple SUMIF should be all you need.
Having just highly rated the indirect function and having updated Numbers on my iPad and iPhone, I now find I can’t use it. Even a simple 1 column x 2 rows table gives an error message. I type “indirect” into a cell and point it to the intersection of column “Item” and row “Test” which should return the result “Fred”. Instead I get an “invalid argument” error. In other scenarios, it even says the cell cannot refer to itself or depend on another cell that references it. Neither of those are the case. It seems to have become unusable. Any help would be greatly appreciated.
Use the forum at my site (macmost.com) and explain what you are doing in detail: the exact function, the values of the cells it is referring to, etc. I'll try to help.
I don't get those little yellow dots when I hover over it? I cam't figure it out...driving me crazy... is there something in the preferences I need to change ??
The yellow dots for extending the data to more cells? First, make sure you have those cells selected. Then you need to move the cursor. Try moving it just above the top of a selection or just below the bottom, etc.
Hello Gary, thank you so much. I have a question: How can I use indirect function to match and compare the cell value as sheet name value. I have it working in Excel like this :Indirect(B$5&"!"&"C5") were B5 value is equal to a sheet name? Thank you in advance
The string looks like this: "Sheet 1::Table 1::B2". To see this, just manually create a formula in sheet 2 that points to a sample cell in a table in sheet 1. Remember that Numbers is: Sheet::Table::Cell. Excel doesn't have Tables.
@@macmost Thanks but the B5 is representing the sheet name and that has the cell address. So In Number I inserted in the other sheet: =INDIRECT(B5&"::"&B5&"::"&C2) after I rename the table 1 to the same as the sheet 1. But it didn't work, can you help please thanks again.
@@rocky54app Debug it. Take away the INDIRECT() and just leave B5&"::"&B5&"::"&C2. Does the value of the cell now look right? Try manually linking that cell and then compare the formula you get for that to the value B5&"::"&B5&"::"&C2 gives you.
@@macmost It gives me May::May::, also its is good the mention that what formula selected, the C2 in this sheet is highlighted which means as you know the it consider the C2 in the same sheet and not the "May" Sheet. The error is "The formula contains an invalid reference." Thanks again
@@rocky54app So is the Sheet name "May"? And is the table name "May" as well? If so, that part works, you just need to fix the last part. Is C2 in the current sheet empty? If not, you've go to fix that reference.
@@macmost Thank you for commenting. I saw the feature in a youtube movie at 5:10 minutes and there you see what I mean. Seems easy to me, can this in Numbers too??? I live in Europe and the video was made in the Netherlands. Bedankt🙏🙏🙏
I LIKE YOUR ALL VIDEOS BUT I DONT UNDERSTAND ENGLISH LANGUAGE VERY MUCH, SO CAN YOU please TRANSLATE YOUR VIDEOS IN (HINDI) LANGUAGE so I easily understand. Thanku
Just discovered this excellent series. I do not have a Mac but these have been so useful on my iPad & iPhone. This particular function has been a real eye-opener. I particularly like the way the items are presented, building from the simple through to the complex. Top marks.
Thank you so much for the excellent tutorials. I grudgingly moved to a Mac a year or so ago and have suffered serious Excel withdrawal symptoms ever since, not realizing that Numbers itself has quite a bit of the same functionality, with a large selection of functions and the ability to use AppleScript with it.
Thanks for offering support at your website. After hours of work, I have finally resolved all my current issues. In case it should help others, on the matter of my simple issue I was making a basic mistake and trying to use “Indirect” to point directly to a cell. The much more complex issue of the error message saying the cell cannot reference itself was because I was putting the function into one of the HEADER columns. It seems like Numbers treats them all as one entity. It was working perfectly in a test table but the column was not set to HEADER. As soon as I did so, it failed. Who knew? I’ve been using quite complex Numbers spreadsheets for years but this issue has never reared its ugly head, before. Thanks for all your good advice👍.
Wow. Finally, got a handle on this. It’s sooo powerful. I’ve got it filtering one sheet, following a filter on another - amazing. Thanks for teaching me about this incredibly useful function. You are the boss.
Thank you SO much, this has really helped me fix an otherwise frustrating problem with my sheet. Thank you again.
Gary, this is a great function I can use, and the popupmenu is also great. Thanks!
Thank you Gary, this helps a lot. I was trying to cross reference bunch of stuff and had real difficulties. This solves a lot.
Excellent video Gary
Thank you so much! I am really starting to like numbers more and more over excel anymore! I just don’t need all that power and rather now the nicer look. It’s a very worth while change
Numbers is way better than excel
@@andrewb293 agreed 100% and getting better all the time
I been trying to do something like this for about 2 years. Thank you
I wish he'd been around when I was working for the Center in Los Angeles. Trying to do it with Excel Worksheets didn't work. It's supposed to be this powerful program but I barely knew how to use it.
When I worked for the same Center in New York City, I used to have to sit next to the box in which people put their "weapons" while they were at the Center. You weren't allowed to carry anything on their grounds that you could use as a weapon.
I find I can create an address using match for both rows and columns, where each of the two match functions uses indirect on the string of table name and column or row references. To complete the address string you also include the other table’s name again, so I do this by reference to a cell where I have entered a table’s name. Finally the whole thing is wrapped in a third indirect statement. This allows me to flexibly combine in one table, data from as many other tables as I require.
Another great video Gary! Very informative!
thank you❤
It’s clear to understand... Thank you!
A+ Content, Thank you so much!
SUUUUUUUPER useful. Thanks!
Hey Gary! You are my numbers hero! Is there a way to use the indirect function across all/future sheets? I have spreadsheets separated by weeks and I continue to add new sheets. I am looking to get an average of specific cells across all and future sheets. Is this possible using indirect?
Great. How can I create a duplicate sheet in Numbers by an AppleScript. Thanks
Hello Gray, can you make a video on how to make school marksheet in numbers. it will be very very grateful for teachers those who are using Mac. Thank You
Appreciate your explanations. On this one, why don't we Hit "equals" than click on the exact box that contains the .60? Why is the "indirect" necessary?
I'm showing you how it works. Later I show examples of why it is useful.
Hello, Very nice videos, I didn't understand how you can mix indirect and drop menu and IF function - Let me explain : I want to set a function for my office like if a patient (NAME) has a pathology then he would receive a different treatment depending on the pathology : Exemple patient A has pathology X that has 3 different treatments TTT 1 TTT2 TTT 3 - Patient B has pathology Z with treatments TTT4 TTT5 and TTT 6. I created a list with all the pathologies and different lisst of treatments per pathology - how can I create a function with drop menu like patient name -next column choose between pathologies - next column choose between treatments which should be different from the pathology. Thank you
This is EXACTLY what I'm trying to do as well! Have you figured it out yet? Any help would be much appreciated :)
Hi! I am looking to do something similar to what you tried doing. Did you ever figure it out?
does the indirect work with looking up a text value as opposed to a numerical value?
It can get any value, sure. Try it and see.
Great tutorial, thanks! Does it work between numbers files? Can I indirect lookup a value on a a table on a a different file?
No, you can't have formulas that access other documents.
Hi, nice info you gave us. But I had tried to change the 60 with a cell and I have alway an error. Where I'm get wrong? INDIRECT(Models::$A$1&"::"&Vdx::$A$1&" 60"). I have 4 tables with the model where I choose from....I have a value Voltage and the 60 is the time (minutes) but I need bring it back from another cell....and I'm failing every time.... how can I substitute the 60 with a reference cell? Thanks Gary
Gary, I already saw your response to P M about 1 month ago. Very useful....help me with my question...thanks
Gary you are amazing
thank you, Gary, please help me in applying black scholas formula of option calculation..secondly in excel we use solver function, how the same can be used in numbers..
Sorry, not familiar with "black scholas formula" and the Solver seems to be an add-on to Excel that performs operations, not a function. If you like these things in Excel, then why not just stick with using them in Excel. The apps are different and Excel will always do some thing Numbers doesn't, and Numbers will always do things Excel doesn't.
Hey bro, do you know how to work functions across sheets? Or to find duplicates across sheets?
Don’t get me wrong excel is still the KING but I sure am liking numbers simplicity and curb appeal even if I’m now the only one who looks...😂 it’s plenty strong enough for me
How to highlight minimum and maximum value in Numbers
Thanks Gary. I am having real trouble trying to use the indirect method to determine the total amount on our churches tithing numbers sheet when some money goes to the building fund and some to general and some to missions and some to youth. How would I calculate that formula? I am really impressed with your videos but this one seems to give me grief.
You can do it any number of ways. Not sure of your exact situation or why you would need INDIRECT to do that at all. Just a simple SUMIF should be all you need.
Having just highly rated the indirect function and having updated Numbers on my iPad and iPhone, I now find I can’t use it. Even a simple 1 column x 2 rows table gives an error message. I type “indirect” into a cell and point it to the intersection of column “Item” and row “Test” which should return the result “Fred”. Instead I get an “invalid argument” error. In other scenarios, it even says the cell cannot refer to itself or depend on another cell that references it. Neither of those are the case. It seems to have become unusable. Any help would be greatly appreciated.
Use the forum at my site (macmost.com) and explain what you are doing in detail: the exact function, the values of the cells it is referring to, etc. I'll try to help.
Can you walk us through making an attendance sheet that adds up all the values for no call/no show, late, leave early etc In numbers
Look at the COUNTIF function. Read the help info on that with the examples.
I don't get those little yellow dots when I hover over it? I cam't figure it out...driving me crazy... is there something in the preferences I need to change ??
The yellow dots for extending the data to more cells? First, make sure you have those cells selected. Then you need to move the cursor. Try moving it just above the top of a selection or just below the bottom, etc.
Hello Gary, thank you so much. I have a question: How can I use indirect function to match and compare the cell value as sheet name value. I have it working in Excel like this :Indirect(B$5&"!"&"C5") were B5 value is equal to a sheet name? Thank you in advance
The string looks like this: "Sheet 1::Table 1::B2". To see this, just manually create a formula in sheet 2 that points to a sample cell in a table in sheet 1. Remember that Numbers is: Sheet::Table::Cell. Excel doesn't have Tables.
@@macmost Thanks but the B5 is representing the sheet name and that has the cell address. So In Number I inserted in the other sheet: =INDIRECT(B5&"::"&B5&"::"&C2) after I rename the table 1 to the same as the sheet 1. But it didn't work, can you help please thanks again.
@@rocky54app Debug it. Take away the INDIRECT() and just leave B5&"::"&B5&"::"&C2. Does the value of the cell now look right? Try manually linking that cell and then compare the formula you get for that to the value B5&"::"&B5&"::"&C2 gives you.
@@macmost It gives me May::May::, also its is good the mention that what formula selected, the C2 in this sheet is highlighted which means as you know the it consider the C2 in the same sheet and not the "May" Sheet. The error is "The formula contains an invalid reference." Thanks again
@@rocky54app So is the Sheet name "May"? And is the table name "May" as well? If so, that part works, you just need to fix the last part. Is C2 in the current sheet empty? If not, you've go to fix that reference.
hey dude how would I copy a full table over automatically to a new tab
What do you mean by "automatically?" Explain with more details and I'll try to suggest something.
Which software do you use to make your recordings?
I use Screenflow. Works really well.
Genius
Is there no wizard function in Numbers like with Excel???????
Nothing exactly like that. But there are other features of Numbers that make things easier, depending on what you want to do specifically.
@@macmost Thank you for commenting. I saw the feature in a youtube movie at 5:10 minutes and there you see what I mean. Seems easy to me, can this in Numbers too??? I live in Europe and the video was made in the Netherlands. Bedankt🙏🙏🙏
ruclips.net/video/5UrzTy3Hxko/видео.html
@@jds4935 There's no mode like that in Numbers. There may be an easy way to do what you want, but I can't tell because of the language barrier, sorry.
@@macmost Thanks for commenting anyway!!!!!
I LIKE YOUR ALL VIDEOS BUT I DONT UNDERSTAND ENGLISH LANGUAGE VERY MUCH, SO CAN YOU please TRANSLATE YOUR VIDEOS IN (HINDI) LANGUAGE so I easily understand. Thanku
Try using a video translation tool (like votch.tv or look up another one)
Can I do multiple dependent pop up menus in numbers? Like in this video: ruclips.net/video/NhtsVTeGywc/видео.html
Lol lol lol