I am trying to drag this formula down an entire column.
=IF(ISBLANK(Sheet1!D3),"",NETWORKDAYS(Sheet1!D3,TODAY(),'INFO TAB'!A3:'INFO TAB'!A32))
I want the D3 reference to change accordingly which it does... so in cell 4 it changes to D4 etc, but I want the 'INFO TAB'!A3:'INFO TAB'!A32 to remain as just that for all cells but it keeps changing those accordingly as well. How can I stop the second part from changing which keeping the first part as is?
=IF(ISBLANK(Sheet1!D3),"",NETWORKDAYS(Sheet1!D3,TODAY(),INDIRECT("'INFO TAB'!A3:'INFO TAB'!A32")))
Note: INDIRECT(" ")
Double quotes around the bit you want to keep
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Great Thanks!
In order to lock cell references in place, use $
$ in front of column (i.e. $A1) keeps the column the same when copying left or right
$ in front of the row (i.e. A$1) keeps the row the same when copying up and down
$ in front of both keeps it the same no matter which direction you copy it in.
So try
=IF(ISBLANK(Sheet1!D3),"",NETWORKDAYS(Sheet1!D3,TODAY(),'INFO TAB'!$A$3:'INFO TAB'!$A$32))
Also, you only need to reference the sheet once within a range so you can shorten it to
=IF(ISBLANK(Sheet1!D3),"",NETWORKDAYS(Sheet1!D3,TODAY(),'INFO TAB'!$A$3:$A$32))
Is that what you were looking for?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks