How to create an Auto list with No Blank Cell -- HELP

IBRA2018 · #24 04-16-2018, 02:07 AM

Quote:

Originally Posted by ArviLaanemets

You left named ranges unedited. I defined all referred ranges as dynamic ones for all formulas, so now the formulas adjust automatically when you add data into sheet 2017.

Edit: About Dynamic Named Ranges/Dynamic Names.
A Name or Named Range is dynamic, when:
1. The value of Name or returned range of Named Range depends on range of data source, e.g. on number of rows in table (MaxRows, DataProj, etc. in attached file);
2. The value of Name or returned range of Named Range depends on position of active cell on worksheet when you define the Name/Named Range.

Nice,,,
I have tried to play arround with the file and add more sheets and also do some changes and I came to face these things:

1st:
I have tried to changed the the column title of H1 ...H2... H3 ... H4 to any name or to
F1 ...F2... F3 ... F4

So when I changed it in the formula:
from:

Quote:

=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),DataH1,0)-1))

To:

Quote:

=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),DataF1,0)-1))

so it can match the Column title it gave me error #NAME?

2nd
Instead of hiding Columns H1 ...H2... H3 ... H4 in sheet "2017"
I created another sheet and gave it a name "1001"
and I changed for formula to read from sheet "2017"

Quote:

=IF(ROW()=EVEN(ROW()),0,COUNTIF('2017'!$G5:$G$6,"I n Prog"))

it worked fine and it can gets the correct reading from "2017"

NOW to get the the final result on Sheet "Report2" I must do some changing on this formula

Quote:

=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),DataH1,0)-1))

I tried to make the changes on DataH1

Quote:

=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),'1001'!DataH1,0)-1))

but I get error

So I have these issues ... I feel I'm getting closer to what I'm looking for

Sheet "Reprot2" is reading from Sheet "1001"
the file is attached

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create Drop Down list to change cell color	bksmith	Excel	2	08-21-2017 02:37 AM
Have a cell filled black when blank in dropdown list	Aussie_81	Excel	14	04-06-2017 04:50 AM
Formulato say if cell is blank do this, if not blank do this.	mbesspiata	Excel	1	01-17-2015 05:02 AM
Auto add a list cell if the last list cell is filled	tasuooooo	Excel	1	07-31-2012 08:40 PM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM