• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 32
  • Last Modified:

Rows to expand to fit data in Excel

I have this form called RFQ and in the blue section for example I wanted it to expand automatically if the data does not fit in the blue highlighted area.  For example if I type in 1000 in M6 that will populate the blue area just fine since its less than 10 rows but if I type in 2000 in M6 that has 24 rows of data and I wanted the blue section to expand to fit it.  Now if I type 1000 again then I wanted it to go back to the normal 10 rows.

I've attached the workbook for reference.  Thanks!
C--Users-lfreund-Desktop-RFQ-EE.xlsm
0
LUIS FREUND
Asked:
LUIS FREUND
  • 7
  • 4
  • 3
  • +1
2 Solutions
 
ShumsDistinguished Expert - 2017Commented:
Hi Luis,

Please check attached...try changing values in M6
C--Users-lfreund-Desktop-RFQ-EE.xlsm
0
 
Matt NicholasBusiness AnalystCommented:
Just create an Excel table and link it to the Access Database?

Run your query which will then adjust the rows in the table dynamically, I would even suggest you import all data into a table in Excel (just like a repository) and then use Power Query to redesign another table which changes with user inputs
0
 
LUIS FREUNDAuthor Commented:
Thanks Shums.....works great but is it possible to adapt this when there could be a lot of numbers, for example it could be anywhere from 1000's to 6000's, depending what the user inputs that relates to 1000's of rows of data?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LUIS FREUNDAuthor Commented:
Good point Matt...I've never done power query inside Excel...
0
 
LUIS FREUNDAuthor Commented:
I've reposted the workbook....
C--Users-lfreund-Desktop-RFQ-EE.xlsm
0
 
ShumsDistinguished Expert - 2017Commented:
Hi Luis,

I can change any number to their counts. Your initial request was for just two numbers 1000 & 2000. I can send your revised code tomorrow.

Regards,
Shums
0
 
Martin LissOlder than dirtCommented:
It does that now.
2018-02-18_10-23-28.mp4
0
 
LUIS FREUNDAuthor Commented:
True.....but I have thousands of row of data and the numbers will range from the 1000's to the 6000's depending what the user does.
0
 
Martin LissOlder than dirtCommented:
Are you saying that there might be a 1000 or more of the same RFQ_NO on the QA page? If so what happens when you enter the number in the form?
0
 
LUIS FREUNDAuthor Commented:
There will be a RFQ numbers against no more than 40 part numbers...but will be a lot of RFQ's numbers
0
 
Martin LissOlder than dirtCommented:
Sorry but I still don't understand the problem you are describing when you say
I have thousands of row of data and the numbers will range from the 1000's to the 6000's depending what the user does.
0
 
LUIS FREUNDAuthor Commented:
on Thousands of row of data there will be RFQ numbers from 1000 to 6000, for example; 1001, 1008, 2009, 2056, 3004, 4001, 5000, 6022...etc.  Depending how the user categorizes a block of part numbers the user will enter the RFQ number associated to those part numbers.

So I would need to enter whatever the RFQ number is from the user to populate the form.
0
 
Martin LissOlder than dirtCommented:
How s that different from what is being done now?
0
 
ShumsDistinguished Expert - 2017Commented:
Hi Luis,

Try changing any RFQ Numbers in M6....
C--Users-lfreund-Desktop-RFQ-EE_2.xlsm
0
 
LUIS FREUNDAuthor Commented:
Thank you Shums!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now