I have two workbooks, one is my data and the other is report from random
areas of my data. I am using the following formula to pull information:
=IF(ISBLANK('[Data.xlsx]Sheet!$F318),"",'[Data.xlsx]Sheet'!$F318)
The column is stationary, but sometimes there are a few rows that I can fill
with data. I need the blank cell if there is nothing in my data file, and the
data I am using is Text, Dates, and numbers so traditional conditional
formatting was not sorting out blanks. An example would be:
=IF(ISBLANK('[Data.xlsx]Sheet!$F318),"",'[Data.xlsx]Sheet'!$F318)
=IF(ISBLANK('[Data.xlsx]Sheet!$F319),"",'[Data.xlsx]Sheet'!$F319)
=IF(ISBLANK('[Data.xlsx]Sheet!$F341),"",'[Data.xlsx]Sheet'!$F341)
=IF(ISBLANK('[Data.xlsx]Sheet!$F348),"",'[Data.xlsx]Sheet'!$F348)
€¦ and so on.

Some cells already have existing links others are blank.
Is there a more efficient way of ether adding the formula to existing cells?
OR
Changing reference cell other than going in to formula and changing number?
(i.e. the = + Click on data cell used to create reference)

I suppose you could use INDIRECT and just have a place to enter your list of
cell references.

So, say in Sheet2, A1:A500, you'd enter the cell numbers you want to
reference as 318
319
341
348
etc.

then, in Sheet1, you have

=IF(ISBLANK(INDIRECT("'[Data.xlsx]Sheet!$F"&Sheet2!A1)),"",INDIRECT("'[Data.xlsx]Sheet!$F"&Sheet2!A1))

and paste down. May want to add another If so you can have this formula all
down your report row, so:

=IF(Sheet2!A1="","",IF(ISBLANK(INDIRECT("'[Data.xlsx]Sheet!$F"&Sheet2!A1)),"",INDIRECT("'[Data.xlsx]Sheet!$F"&Sheet2!A1)))

"PhilosophersSage" wrote:

I have two workbooks, one is my data and the other is report from random
areas of my data. I am using the following formula to pull information:
=IF(ISBLANK('[Data.xlsx]Sheet!$F318),"",'[Data.xlsx]Sheet'!$F318)
The column is stationary, but sometimes there are a few rows that I can fill
with data. I need the blank cell if there is nothing in my data file, and the
data I am using is Text, Dates, and numbers so traditional conditional
formatting was not sorting out blanks. An example would be:
=IF(ISBLANK('[Data.xlsx]Sheet!$F318),"",'[Data.xlsx]Sheet'!$F318)
=IF(ISBLANK('[Data.xlsx]Sheet!$F319),"",'[Data.xlsx]Sheet'!$F319)
=IF(ISBLANK('[Data.xlsx]Sheet!$F341),"",'[Data.xlsx]Sheet'!$F341)
=IF(ISBLANK('[Data.xlsx]Sheet!$F348),"",'[Data.xlsx]Sheet'!$F348)
€¦ and so on.

Some cells already have existing links others are blank.
Is there a more efficient way of ether adding the formula to existing cells?
OR
Changing reference cell other than going in to formula and changing number?
(i.e. the = + Click on data cell used to create reference)