Indirect function and processing power.

[Eamonn100]

I was wondering which of these two indirect formulas (that preform the same calculation) would use less processing power and not slow Excel down as much? Looks can be deceiving so I thought I'd ask those who would know for sure.



=INDIRECT($A$1&CELL("address",$b3))

=INDIRECT($A$1&ADDRESS(ROW($b3),COLUMN($b3))))

 

[Eamonn100]

Do you need to use the INDIRECT function? Can you explain what you are wanting to achieve?

For now I have to use INDIRECT function as I don't have the time to do a big change,.......but in the future if there's a quicker way for sure I'll use that.


I have 10 different sheets of data. When certain conditions are met I move most of the data on a row one cell to the left to make room for new data. On a separate calculation sheet I use the indirect function so that I can jump between the different sheets using their names.


(This idea just came to me) If I used a individual calculation sheet for each of the 10 sheets and just referenced each cell. Would that use less processing power than INDIRECT? Generally I only have 1 of the 10 sheets and the calculation sheet open at any time.


Like I said above, I'll change it to be faster in the future. What I have now is me finding out about Excel and thinking as I go so it's not the most efficient way. If I knew what I know now when I started things would be laid out different. The day I learnt about INDIRECT I thought it was really cool and ran with it. Not even knowing that it used up a lot of processing power.


Basically if there's a simple way of moving some data within a row one cell to the left when certain conditions are met,.... I'm all ears.

 

[Andrew Fergus]

I understand the excitement of finding a new function and then using it to increase your knowledge. We have all been there! However, as you have found out it may not be the best solution.

I'm sure what you want to achieve can be done. It's a question of correctly defining the problem. So far what we have are desired methods (e.g. shifting the data one cell to the left - this is a part solution, it's not the actual problem). If we could correctly define the problem then we can come up with a solution that is in all likelihood much faster and more efficient than using INDIRECT. I say this because INDIRECT is very rarely the most efficient way. For example, if the problem is that you are trying to reference some data but the data you want is constantly shifting, then that is a better description of the problem which can easily be solved.

Maybe if you shared your data layout per the 10 sheets (which I'm assuming are all similar) and what you are trying to do with the last sheet, then someone somewhere will know a better way of doing this. You may also find it doesn't take long to implement.

Late edit: yes your suggestion would be much faster, but again I'm not sure it would be particularly efficient. I'm also not understanding why you need to move the data to the left - have you run out of columns for your data?

 

[Eamonn100]

Hi Andrew thanks for getting back to me.

This will have to wait until the weekend, I'll get back to you then. I'm snowed under and have no spare grey matter at the moment, haha.

Thanks.

 

[Eamonn100]

Excel 2013/2016

	A	B	C	D	E	BF	BG	BH	BI
INDIRECT reference cell

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.000[/TD]
[TD="align: right"]2.000[/TD]
[TD="align: right"]3.000[/TD]
[TD="align: right"]4.000[/TD]
[TD="align: right"]57.000[/TD]
[TD="align: right"]58.000[/TD]
[TD="align: right"]59.000[/TD]
[TD="align: right"]60.000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

Ok, above is a 60 cell example of what I'm doing. Normally I have two hundred 60 cell sections on each row. Starting in B2 is this formula. It is dragged over and down.

=IFERROR(IF(DATEDIF(INDIRECT($A$3&CELL("address",$NKV3)),INDIRECT($A$3&CELL("address",$NML3)),"M")>=12,INDIRECT($A$3&CELL("address",ES3)),INDIRECT($A$3&CELL("address",ER3))),INDIRECT($A$3&CELL("address",ER3)))



The formula calculates the difference between 2 dates on another sheet. If the difference between the two dates is 12 months or greater it returns the next cell to the right. If an error or less than 12 months it returns the current figure, (stays the same).

Like I stated in earlier posts. Because of the large number of INDIRECT function it calculates extremely slow. Can anyone think of a way of updating and moving a group of cells to the left when certain conditions are met?

 

[Fazza]

hello, Eamonn

It isn't clear what is wanted. If you want some help on this, I suggest you start a new thread. Please take some time to clearly explain the situation. The better your question: the better an answer you'll receive.

20 milllion uses of INDIRECT - it certainly seems like better approaches will exist. Maybe orders of magnitude faster/ more efficient.

regards

 

[Eamonn100]

hello, Eamonn

It isn't clear what is wanted. If you want some help on this, I suggest you start a new thread. Please take some time to clearly explain the situation. The better your question: the better an answer you'll receive.

20 milllion uses of INDIRECT - it certainly seems like better approaches will exist. Maybe orders of magnitude faster/ more efficient.

regards

Hello Fazza,

thanks for the advice. I've started a new thread with this problem. I worry I still haven't explained it clearly. I'm quite dyslexic so what I read back to myself and what the rest of the world reads can be two different things. Could you please take a look at the new thread to see if what I've wrote is clear and understandable from at least an Excel point of view.

https://www.mrexcel.com/forum/excel-questions/1052495-indirect-function-alternatives.html

Thanks.

 

[Fazza]

Sure, Eamonn

I did see that new thread when it was created. It wasn't clear to me.
No clarity of what was happening, nor why it was being done that way.
The single example didn't give an idea of the bigger picture: which I think is needed to suggest a good approach.
Perhaps there is someone you work with who can help? Sometimes a second set of eyes offers a new perspective.

And, btw, when starting a new thread it is much better to leave it as a single post: then it filters as unanswered. As soon as it has a second post it totally drops off in priority.

regards, Fazza

 

[Eamonn100]

Sure, Eamonn

I did see that new thread when it was created. It wasn't clear to me.
No clarity of what was happening, nor why it was being done that way.
The single example didn't give an idea of the bigger picture: which I think is needed to suggest a good approach.
Perhaps there is someone you work with who can help? Sometimes a second set of eyes offers a new perspective.

And, btw, when starting a new thread it is much better to leave it as a single post: then it filters as unanswered. As soon as it has a second post it totally drops off in priority.

regards, Fazza

Thanks for that Fazza,

you've made me realise it's not the dyslexia,......... it's just that I'm as burnt out as a 27 years old rock star,....... and the dyslexia. I'm taking a 2 week break from this, Then I'll look at it again and get someone else to look at it too before posting.

It's official! I'm a mental basket case at the moment.

Thanks.