Hi
I need assistance
1) I have worksheet (form) which I want to print for hard copy records. How to autopopulate the dates based on the number of instalments.
2) I have created a hyperlink to link the worksheet, is there a way, using excel formula, that the cursor will go the the last cell when we click the link.
Thank you.
Nancy
For your question #2. I am not certain that I understand your meaning, and I am not well experienced with Hyperlink(s). I could not help but noticing reference to a Named Range in the Hyperlink editor. If that can be accessed and used somehow then perhaps a Dynamic Named Range formula in Name Manager could be of some use to you. To verify this does what you want copy this formula. Then from the Form sheet paste it into the name drop down box (upper left hand corner) and hit enter. It will take you to cell B12 on the Forms sheet......the last filled cell.
If this looks like it could be of service then create a DNR in Name Manager and paste this formula into the 'Refers to:' dialogue box.
To use it in formula simply refer to it by the name you've assigned to it.Formula:
=INDEX($B$10:$B$21,MATCH(1E+306,$B$10:$B$21,1))
Hoping this will help.
Working on the auto-populate part.
Last edited by FlameRetired; 04-22-2015 at 01:01 AM.
I am not clear on exactly what you want. I'll start with this in B11 of your Form sheet.Fill down. Then this in D11 and fill down.Formula:=IF(DATE(YEAR($B$10),MONTH($B$10)+ROWS($1:1),DAY($B$10))<INDEX(Data1!$D$4:$D$8,MATCH(Form!$F$3,Data1!$A$4:$A$8,0)),DATE(YEAR($B$10),MONTH($B$10)+ROWS($1:1),DAY($B$10)),"")Formula:=IF(($B11<>"")*($B11<>0),$F$5,"")
These will auto-populate, but I am not sure this is how you want it to happen.
By the way the uploaded workbook had calculation set to Manual. I changed it to get the formulas to work.
Workbook is attached.
Hi
It works. However, short of 1 instalment. For eg. if last payment for Contract 5 is on 16 Mar 15, the form only show till 16 Feb 15.
Please assist.
Thank you.
Try this modified formula in B11 of Form sheet and fill down.Formula:=IF(DATE(YEAR($B$10),MONTH($B$10)+ROWS($1:1)-1,DAY($B$10))<INDEX(Data1!$D$4:$D$8,MATCH(Form!$F$3,Data1!$A$4:$A$8,0)),DATE(YEAR($B$10),MONTH($B$10)+ROWS($1:1),DAY($B$10)-1),"")
Fantastic! Thank you.
You're welcome. Glad it works and thanks for the rep.
(and thank you for marking this thread [SOLVED].)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks