Reference cell on other sheet when not blank

[detriez]

I have a workbook with two sheets: [Lead Template] and [CSV]

I need to reference [Lead Template] cells onto [CSV] for all populated rows and columns on [Lead Template]

I am using this formula on [CSV] and I can copy it down and across but that would bloat my file

=IF(ISBLANK('Lead Template'!A1),"",'Lead Template'!A1)

I feel like something like this might work but I'm not quite sure how to bring this all together
I'm hope to make this passive or when [CSV] is selected or has focus

lRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
=IF(ISBLANK('Lead Template'!A1),"",'Lead Template'!A1)

 

[detriez]

Edit to the original post

I need to share this on oneDrive which I guess will not support VAB/Macros so, I think my only option is a formula.

 

[jtakw]

Hi,

I must be mis-understanding your question.

1. your formula:
=IF(ISBLANK('Lead Template'!A1),"",'Lead Template'!A1)

is the Same as ='Lead Template'!A1

There's no need to check if A1 is Blank to return a Blank, it's the same, if A1 is Blank, your result will be Blank.

2. Why not just copy the entire sheet 'Lead Template'!A1 and paste Value to CSV?

 

[Fluff]

if A1 is Blank, your result will be Blank.

Not for me it isn't, if A1 is blank I get a 0 in the cell.
Not sure if that's just a version difference or not.

 

[jtakw]

Actually you're right, Excel evaluates a blank as 0 in a formula...my bad.

But I still think Copy/Paste as Value should do what OP wants?