Locking different values within cell depending if they are dragged vertically or horizonta

Hello all

I'm having difficulties figuring out a solution on how I can lock certain references within my formula depending upon whether I drag it along the row or down the column.

As I drag the formula along the row I require this;
=IF(FZ3=Import,VLOOKUP($A$8,'Edited Data'!$O$3:$P$3600,2,0),FZ3)

But if I am to drag the formula down the column then I need this;
=IF($F$Z3=Import,VLOOKUP(A8,'Edited Data'!$O$3:$P$3600,2,0),FZ3)

The key differences of those two formulas being the IF function reference cell (FZ3) and the VLOOKUP reference cell (A8).


Thanks!

Martin

Hi,

In your post you show $F$Z3, shouldn't this be $FZ$3 ?
Either way, FZ shows figures not "Import"
Are you able to attach a copy of your file so we can take a look?

Regards

peterrc

Hi


In your post you show $F$Z3, shouldn't this be $FZ$3 ?
Either way, FZ shows figures not "Import"

$FZ$3 is correct, that typo arose from me editing in here here. I had manually gone through and changed the absolute and relative references but have realised that is ridiculous.
"IMPORT" is a name box which is cell A1.
I have no issues at all with absolute or relative references, in this formula they need to vary depending on whether they are dragged horizontally or vertically.

A trimmed down version of the workbook is attached (size far exceeded limit). I am working in the sheet "Data Compilation". The formula is to reference the date of the data from sheet "Edited Data" match it to the corresponding date in the current sheet,
then LOOKUP the corresponding Stock Exchange (ASX) code in column A and import the data.

=IF(FZ3=Import,VLOOKUP($A$8,'Edited Data'!$O$3:$P$3600,2,0),FZ8) - A8 is the reference to the ASX code and needs to be absolute when dragging horizontally.
=IF($FZ$3=Import,VLOOKUP(A9,'Edited Data'!$O$3:$P$3600,2,0),FZ9) - FZ3 is the reference to the current data date and needs to be absolute when dragging vertically

Thank you.

Right ok, thanks for pointing that out.

In the case of FZ3 not equally Import, then the cell value remains unchanged

=IF(FZ$3=Import,VLOOKUP($A8,'Edited Data'!$O$3:$P$3600,2,0),FZ8


Thanks for the assistance

Why can it not equal itself? If the IF function argument proves false then the value that is already in the cell remains unchanged (including a zero value). This part is critical as data imported from previous days isn't wiped when daily data is imported.

Or can you see a better way to achieve that same outcome?

I can because I have and it works.

Have you had a look inside the workbook? It somewhat difficult to explain thoroughly here; it references a list of sorted data on another sheet and inserts it for that day.
All data from previous days is left untouched as the IF function is false and thus the cell equals its original value.

I am aware of that and as everything is generated by macros, there is no requirement to click on the formula. In absence of a more bulletproof approach, this is working for me.

Completely agree - once the formula is in place, if I have to change it then I will be required to go back and import the data again for each day.

Is there any other way you can see about getting around this?

I agree with gak67, Post #14.
The reason it "appears" to be working for you is because you have Calculation set to Manual.
With Calculation set to Automatic, the result in FY8 is 0.
If this is the wrong answer then there is a fault with the formula you are using.

Regards

peterrc

Can you see a solution for this issue Peter? I'm stumped as to what else I can do....

Hi Sonogan,

I order to try and help I have a couple of questions:-

1. In Modules 2 and 3 of your VBA code you have either a "Disable Autocalc" or a "Disengage Autoclac" macro.
Why would you want to disable autocalc ?
2. Are columns, FY:GC dependant on cell FZ3, or is only column FZ dependant on cell FZ3 ?

Please advise.

Regards

peterrc

I disable auto calculate as I am using importing data into another sheet, sorting it and then checking it prior to then importing it to my final sheet.

Only column FZ is dependent on FZ3. Same goes for every other column (FY is dependent on FY3 etc)


Thanks.