Referece to Formulas R1C1

[fredlo2010]

Hello guys,

I have to create a workbook and populate some formulas that have lookups to closed workbooks.

I am trying to make the ranges for the lookup as dynamic as possible. I also want to use a R1C1 formula because it will allow me to populate several cells at the same time in one step. I can always put a regular formula in the first cells and the fill. But I would rather not do that.

So the first thing I do is open my Reference workbook and load the addresses for the ranges I will be doing the lookup to. Once those ranges are in my string variables I would use them in my formula.

The only problem is that If the formula references to a column before the the column I am putting the formula in then it comes with the negative value.

Any ideas on how to do this?

Thanks

[fredlo2010]

Well this was actually me being silly.

The formula works perfectly.

I made a mistake when writing the path for the VLOOKUP I swapped positions for "!" and " ' " for the sheet name.

Thanks for all the help.

[mdmackillop]

If your lookup is in the "wrong" order, use Index-Match
=INDEX(A:A,MATCH(E1,B:B,0))

[fredlo2010]

If your lookup is in the "wrong" order, use Index-Match
=INDEX(A:A,MATCH(E1,B:B,0))

Thanks a lot for the help but this is not the case. The lookup was in the correct order.

I will be using Vlookups because the ranges are big (more than one column) so it will be easier to reference them like that. In my formulas which are complicated already.


Thanks

[Bob Phillips]

I am trying to make the ranges for the lookup as dynamic as possible. I also want to use a R1C1 formula because it will allow me to populate several cells at the same time in one step.

You can just as easily do that with an A1 formula, like so

Range("M1:M20").Formula = "=IF(A1>20,A1*B1,A1)"

and Excel will adjust them all.

Where R1C1 is really useful is in allowing you to embed variables in the formula and use numeric values, noit having to convert to column letters.

[Bob Phillips]

If your lookup is in the "wrong" order, use Index-Match
=INDEX(A:A,MATCH(E1,B:B,0))

OR

=VLOOKUP(E1,CHOOSE({1,2},B:B,A:A),2,FALSE)

[fredlo2010]

You can just as easily do that with an A1 formula, like so

Range("M1:M20").Formula = "=IF(A1>20,A1*B1,A1)"

and Excel will adjust them all.

Mind opening I always thought It could be done only with R1C1 formulas.

Where R1C1 is really useful is in allowing you to embed variables in the formula and use numeric values, noit having to convert to column letters.

I am finding the specific header, returning the column number and then inputting the value in my formula.

Thanks a lot for the help guys

Alfredo

[fredlo2010]

One last thing to add here. I had to work on Saturday fixing all my VLookUps formulas after my code broke when trying to implement a new change and I spent 3 hours on Friday trying to see what was wrong; it was of course the column numbers in my Lookups.

I changed them all to Index-Match combinations and now the only thing I have to worry about is if the headings for my file change(most likely they will not) I also broke free from static column structure where all columns had to be in an specific order to be accessed; so now I can have actual data to the left and all auxiliary formulas to the left.

This took me a whole morning of migration and the creation of a small new class to help me keep the data referencing organized; but it's so much better.

I though I would share this here; because this looked to me like a classic case of "I told you" lol

Thanks

[SamT]

UDF:

'Name is acronym of function Column 2 Index

Public Function fC2I(array_tableFirstColumn As Range, ColIndex As Range)As Long
  fC2I = (ColIndex.Column - array_tableFirstColumn.Column) + 1
End Function

Usage:
Col_index_num "= fC2I(" & Select Cell in any row in first column of table_array, & comma, then select cell in any row in index_column & ")"

=VLOOKUP("XYZ",G2:W999,fC2I($G$1,$H$1),1)

Where you want the return from the second column of the table G:W