Formula reference formatting


P

PhilosophersSage

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)
 
Ad

Advertisements

S

Sean Timmons

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replacement Vlookup Formula 2
Conditional format does not work for some cells 2
Blank Cell 5
Blank 3
Auto-adjusting Formulas 1
Nested if issue 1
deleting rows messing up formulas 2
isblank 2

Top