# Dynamic formulae in Excel

daba 00:23 14 Jan 2005
Locked

I have an application that requires the need to dynamically change part of a cell's formula with the textual contents of another cell on the same row.

The cell actually specifies the name of a worksheet, that the formula in the other cell needs to do some lookup functions in.

Simplified Example :-

Cell A1 : "CP100" {is the target worksheet name}

Cell B1 : "FRED" {is the data to search for on the target sheet}

Cell F1 : =VLOOKUP(B1,'CP100'!A\$1:F\$6,2,FALSE) {search for B1 in the table data on worksheet CP100, and if found return the data in the adjacent right cell}

Now if I want to find the data on another worksheet, eg. CP101, I have to edit the VLookup formula manually, changing CP100 to CP101.

Is there a way of dynamically changing the worksheet reference inside the formula by reference to the data in A1 ?

JonnyTub 00:25 14 Jan 2005

As much as i would like to be able to answer your question, it's too bloody hard for me as i don't use excel much :-)

VoG should have an answer for

JonnyTub 00:26 14 Jan 2005

you

JonnyTub 02:18 14 Jan 2005

Chris the Ancient 09:32 14 Jan 2005

I just hope that VoG proves me wrong (please!), but I've never found a way to do what you want to do. It would have saved me a lot of time and money a couple of years ago if I could.

Now, VoG. If that ain't a challenge! ;o)

CtA

seedie 09:39 14 Jan 2005

You can calculate which cell to refer to by using INDIRECT and ADDRESS. I had to refer to a cell which moved along according to the week number on a newspaper account sheet I did about a year ago.

Hope this helps

CD

seedie 09:49 14 Jan 2005

The cell which worked out which cell to refer to

The *8 is the number of cells between each week ie 7+space.

VoG II 13:01 14 Jan 2005

Yep, INDIRECT is the way to go.

daba 22:18 14 Jan 2005

Thanks for the kick in the right direction, seedie and VoG™, although there is no need for ADDRESS.

Sorted with the formula in C1 :-

=VLOOKUP(A1,INDIRECT(B1&"!A1:B4"),2,FALSE)

Where A1 is the object to search for

B1 contains the worksheet name

A1 to B4 on the target worksheets contains the table in which to search, there is no need to \$ them as they are inside ""

2 returns the data in the cell adjacent to the found A1

FALSE as always says "exact match please".

Thx again

daba 01:07 16 Jan 2005

... I've managed to do what I want without ANY cell formulas, and as a bonus, INDIRECT has opened a doorway to using conditional formatting by reference to another worksheet, normally prohibited by Excel.

If anyone wants a look at how I solved the original problem, and to see the C/F in action (small example file, 22Kb), mail me and I'll be happy to send it.

Now all I've got to do is apply the techniques to the REAL application file, when I get a few spare hours....

daba 01:16 16 Jan 2005

.. if anyone had any opinion of whether vba code would execute faster than conditional formatting in this case - I really want to get the calculation time of this application down to something more reasonable.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy A8 review: Hands-on

Illustrator Juan Esteban Rodriguez on creating highly detailed official film posters for Star Wars…

iMac Pro review

Quelle est la meilleure application de podcast pour Android (2018) ?