Log In
Register
Home
Active Member
June 26, 2020
Offline
I am experiencing this excel problem:
I am using Windows 10 and Microsoft 365
Sheet2!D1:D20 is populated from Sheet1!L4:L24 via formulas.
Sheet1!D1:20 is
- a) formatted as a Table with Heading in B1 - tblNames (or as a Named Range)
- b) Sheet2!D2:20 named as a Range - rngCombs
- c) rngCombs used as drop downs
The problem is that rngCombs does NOT expand as more cells are populated.
Named Ranges within a Table expands dynamically when populated by TYPING the info in the cells within the range.
In Files/Options/Proofing the three boxes in AutoFormat as you type are all three ticked.
How can I solve this problem without VBA.
Thank you
October 5, 2010
Offline
Hi Josia,
Please post a workbook when you ask a question so we don't have to recreate your workbook. It also makes it harder for me to visualize everything and I might make a mistake when creating the layout.
You can use a dynamic named range for rngCombs
=Sheet2!$D$2:OFFSET(Sheet2!$D$1,COUNTA(Sheet2!$D$2:$D$100),,1)
Regards
Phil
Active Member
June 26, 2020
Offline
Hi Philip
Thank you for the response.
I am attaching my Workbook "Subject Allocation 3.4 Tables 20200625.xlsx"
Please see the yellow in the sheet Info!
My problem is in Gr 4 (D20:D35) through to Gr 12 (L20:L35)
Sheet Input populates Sheet Info.
Sheet Allocation is the working sheet where everything comes together.
I want the table or ranges Gr_4 .... Gr_12 to be used as drop downs in the sheet Allocation, but it MUST be dynamically.
Surely trust you will be able to help me.
Thank you
Sias
Active Member
June 26, 2020
Offline
Hi Philip
Have you perhaps had a look at the workbook I have sent you.
I would appreciate it very much if you could assist me in getting the tables to expand dynamically.
I did use the formula you supplied me
=Sheet2!$D$2:OFFSET(Sheet2!$D$1,COUNTA(Sheet2!$D$2:$D$100),,1)
but the problem with this formula is that it leaves blank spaces at the bottom of the list which I would like to prevent.
Thank you
Sias
October 5, 2010
Offline
Hi Josias,
It's a bit confusing because your initial post, subsequent post and attached workbook don't all agree. For example, you don't have a Sheet2 in the workbook. It just takes a lot longer to figure things out in a situation like this.
You can still use a dynamic named range but need to make a few other changes.
On the Info sheet, I've modified the formula in D20:D35 to
=IF(Input!$L$4>0+$C20-1,$D$19&$B20,0)
so a 0 is the 'empty' result when you don't want that cell in the data validation list.
I've changed the formats of D20:D35 to not visually display 0, but you can use COUNTIF to count cells that are not 0. This will generate the dynamic DV list.
I've create a new name GR4_DV that refers to
=Info!$D$20:OFFSET(Info!$D$19,COUNTIF(Info!$D$20:$D$35,"<>0"),,1)
and the DV list in B6 on the Allocation sheet uses GR4_DV.
Please see attached, I trust you can make the modifications for the other ranges on the Info sheet and create the necessary names and DV lists from my example.
Regards
Phil
Active Member
June 26, 2020
Offline
Dear Philip
Thank you so much for the answer and please accept my apologies for the confused way in which I responded.
I was anxious to get the problem solved that I did not clearly think about the information I was giving.
Warm regards
Sias
1 Guest(s)
