• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indirect Formulas

M

mistryman

New Member
Had quick question just looking to grasp a better understanding of indirect formulas the advatages and disadvantages.


I have been tasked with deconstructing a sheet that uses alot of indirect formulas. When I mean alot I mean alot. The indirect formulas reference costs which is then used for analysis .Now I have just figured out that the sheet is referencing from multiple sheets which I cannot avoid due to the way the end data.


So I guess the question is what are the advantages and disadvantages of using indirect formulas to reference and are there other ways to do this.


I hope I followed the rules on posting a question!


Thanks
 
Welcome to the forum!


INDIRECT is handy for creating dynamic formulas, or when you have a list of sheet names and need to build formulas that need to be copied and rather than changing cell reference, you're changing sheet reference. Also handy if you ABSOLUTELY POSITIVELY always want to reference the same cell reference, even if someone adds/deletes other cells.


Disadvantages are that it can't be used to reference external workbooks, and it is a volatile function, which means that it gets recalculated whenever there is any change made in the workbook. Thus, a lot of INDIRECT functions will lead to slow recalculation times.


To know what alternates are available, we'd need to know more about what the formula is trying to do.
 
So here is what the bulk of my formulas look like


=IF(ISERROR(VLOOKUP($VV$4,INDIRECT("'"&$B6&"'!$d:$as"),WQ$3,FALSE)),0,(VLOOKUP($VV$4,INDIRECT("'"&$B6&"'!$d:$as"),WQ$3,FALSE)))


Again this pulling costs from other sheets which are populated and used for reporting.
 
Montrey,


Can you elaborate on how to use INDEX and MATCH for sheet selection?
 
Hi ,


One way to reduce computation time is to eliminate as many repetitive calculations as possible ; thus , in your example , if the expression for the INDIRECT function is used in several cells , then it may be help to put this expression in a separate cell e.g. have the formula :


="'"&$B6&"'!$d:$as"


in C4 , and use INDIRECT(C4) , so that the calculation of the expression "'"&$B6&"'!$d:$as" happens once instead of multiple times wherever it is used.


Narayan
 
Not sure if this will actually work, but it's a thought. You could select the cell that has the formula in it, then define a Named Range (call it MyTable) as:

=INDIRECT("'"&$B6&"'!$d:$as")


Then, all your formulas become:

=IF(ISERROR(VLOOKUP($VV$4,MyTable,WQ$3,FALSE)),0,(VLOOKUP($VV$4,MyTable,WQ$3,FALSE)))


I'm unsure if this truly reduces the amount of calculations, but it might be worth a shot.
 
Check this out.

=VLOOKUP(Y5,INDEX((range1,range2),,,linkedcomboboxcell with sheetnames in same order as index array),1)


Then use Ctrl+shift+enter
 
@Montrey,


That is pure genius. I would never have thought using the INDEX area number ability.


PS. INDEX doesn't seem to like having the multiple areas come from different sheets. Workarounds?
 
@Montrey

Hi!

Good job, man.

Regards!


@Luke M

Hi!

Should we keep an eye on this guy...

Regards!
 
Thanks for the compliments guys! Wewt!

@Luke

a workaround is this

=VLOOKUP(M2,CHOOSE(1,range1,range2),1)


not the same unfortunately but it works :)
 
Beautiful Montrey. =)


Tying it into OP's formula, I think it comes out to something like:

=IF(ISERROR(VLOOKUP($VV$4,CHOOSE(DropDown,Range1,Range2,etc),WQ$3,FALSE)),0,(VLOOKUP($VV$4,CHOOSE(DropDown,Range1,Range2,etc),WQ$3,FALSE)))
 
Hi, in regards to reducing the calculation/computation strain on the workbook, do you recommend using index/match vs. indirect for retrieving data?


I'm hoping this is the correct place to ask this question :) I googled "indirect" trying to understand the benefits for setting up a new globally accessed spreadsheet that needs flexibility, but also low computations since again it's accessed among several global sites on a shared network drive that can be strained.


Thanks!
 
Olddirtybaird


Firstly, Welcome to the Chandoo.org forums


Can I please re-direct you to read the 4 Sticky Green Posts at: http://chandoo.org/forums/

These posts outline how Chandoo.org Forums operate.


You will see that you should not ask new questions on top of an existing post.

This makes it difficult to search and also difficult to follow conversations


You are welcome to start your own post here: http://chandoo.org/forums/?new=1


Indirect is typically used to setup an input Range where the Address changes based on the values in other fields or cells

This address may then be used within any other function including Index/Match combo's

If you know the address or it isn't changing there is no need to use Indirect
 
You must log in or register to reply here.
Back
Top