Dynamic formulae in Excel

  daba 00:23 14 Jan 05
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 05

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 05

you

  JonnyTub 02:18 14 Jan 05

be patient he will reply

  Chris the Ancient 09:32 14 Jan 05

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 05

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 05

The cell which worked out which cell to refer to


IF(WEEKNUM(TODAY(),2)>GZ24,0,INDIRECT(ADDRESS(A22,A24*8,4)))

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

  VoG II 13:01 14 Jan 05

Yep, INDIRECT is the way to go.

  daba 22:18 14 Jan 05

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 05

... 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 05

.. 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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…