Cell/Worksheet reference based on a formula

[cotatliad]

Cell/Worksheet reference based on a formula

Is there a way to refer to cell and worksheets in a way that they can change based on a formula?

Something like:

Instead of =A1, (I want the row reference to change based on a formula but not the column).
something like =A”if(b1=TRUE, 1,2)”

Or instead of = Workpaper1!B7
Something like =’if(b1=TRUE, “Workpaper1”,”Workpaper2”)’!B7 (I want the worksheet reference to change based on a formula but not the cell).

This would save me hours of work. Any help is much appreciated.

 

[Jonmo1]

Welcome to the board...

For your first example, try
=INDEX(A:A,IF(B1,1,2))

For the 2nd, try
=IF(B1,'Workpaper1'!B7,'Workpaper2'!B7)


Hope that helps..

p.s.
If those were simplified examples of something more complex...
Then it is likely that the solution is also more complex
Don't be shy, tell us exactly what you want...

 

[cotatliad]

Thank you for the response. Perhaps I did over simply.

The task I am working on is two steps:

First, I have an analysis template. The template analyses data from a large data sheet based on the name of an account. The template is set with an =cell() function to only extract data from the rows where the account number matches the worksheet name. so first task is to create multiple copies of that template and rename each based on an account list I got. I can do this manually but it is time consuming.

Second task is to reflect the results of analysis on a summary tab. Basically there will be a list of account numbers and I want each row to reflect a particular cell from the work paper that matches the account name.

Due to the amount of data and large number of accounts doing this with an if function is very impractical.

Let me know if I made no sense and I will try to explain what I am trying to do better. Thank you very much!!

 

[Jonmo1]

Perhaps the Indirect function will help..
Although it gets expensive when used alot...

=INDIRECT("'" & B1 & "'!B7")

Where B1 holds the name of a sheet, say WorkPaper1

This effectively evaluates to
='WorkPaper1'!B7

You can then change B1 to any valid sheet name you like..

 

[cotatliad]

Genius!!! I can’t believe I never heard of this function before it does exactly what I needed for the second step. THANK YOU VERY MUCH!!

Any chance you know how to create multiple copies of a template and rename them based on a list???

 

[cotatliad]

One little way to improve it is:

=INDIRECT("'" & D9 & "'!"& C9 &"")

D9 is the worksheet reference and C9 is the cell reference, so neither is fixed. Great function! Thanks again!

 

[Jonmo1]

Glad to help...

Just remember not to get carried away with indirect.
It's a volatile function, meaning it recalculates every time anything is changed, regardless if the change was related to that formula or not..
So the more you use it, the slower your calculations get.

Generally speaking, indirect should only be used if the sheet name needs to be varied..
Don't use if you only need to vary the cell address, there's almost always a better way...