[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Excel Dynamic Range that creates a Pivot Table which Updates a Validation List - List has extra spaces - ???

I have had several questions that have fixed problems that have led me to various solutions all of which now culminate in my Input Form having a list of Valid Pay Period Ending Dates.  Only one problem.  My validation list which gives me my drop down list for the clerk to select from has an extra blank line following each line of data from which they select.

I've attached a sample Excel 2007 Macro Enabled wkbk.  The problem is on sheet "Payroll Data Input cells W1 and V4.  They both do the same thing.

Anyone see where the problem is?
PR-Summary.xlsm
0
wlwebb
Asked:
wlwebb
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

your current formulas return more than one colum. Hence the blanks interspersed. Make sure to include only the columns you need.

change your formula for the range name PayDateCalendarYear to look at column B only, not row $1:$1048576

='Pay Dates'!$B$5:INDEX('Pay Dates'!$B:$B,MATCH(10^300,'Pay Dates'!$B:$B,1))

and for PayPeriodsEndingForCalendarYr use

='Pay Dates'!$E$6:INDEX('Pay Dates'!$E:$E,MATCH(99^99,'Pay Dates'!$E:$E,1))

When you copy and paste these formulas from EE to the Name Manager, quote signs may be added. Make sure to remove them before confirming the formula.

cheers, teylyn
0
 
wlwebbAuthor Commented:
Perfect Teylyn!  I am beginning to understand what you did.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now