Excel range for data based on cell value

0
votes

I am trying to create Excel which will extend or decrease range based on cell value.

I have 3 columns (cell for data, formula1, formula2)

I want to type number in to cell under "Number of units" and than generate for example 1000 rows of these 3 columns (cell for data, formula1, formula2)

Formula 1 - =IF(K5=0;"";K5) is on every row same (I have defined list)

Formula 2 - =IF(G5&H5="";"";(G5&","&H5)) needs to increment by 1 on every next row

Can you help me, how to achieve this function?

Here is a screenshot for better illustration:

Screen here

Thanks for every advice.

1
excelexcel-formularangevba
If you are looking for a VBA solution then please post what you have tried already and we can work off of that. If you're happy with a formula solution that will just show blanks for the rows over the number of results that you are after I can knock that up quite easily - Glitch_Doctor
I don't necessary need Vba. I'll be satisfied if it will work. In some cases when I have 20k rows and rows must be prepared for this number of data, Excel file is too large and computer in work have problem with it . I want to have it light for cases when I need to add just some data if you understand me :) - Pepek Trejbal
oh well, perhaps file size reduction may suffice. do all the computers at work run Excel 2007 or better? if so consider a binary spreadsheet (save as). Also consider whether you need column H with formula for ID_TYPE when it is at the end of your output anyway, you could compress this to one formula. the formula work around will only increase the file size... - Glitch_Doctor
Also, SO is not a free coding service, give it a go yourself and if you come into any problems post the problems you face here and we can work together to overcome them, there's plenty of tutorials out there. come back to us with something even if it is really bad xD - Glitch_Doctor

1 Answers

0
votes

Presuming you want to look up the n values from a table matching your type, where n is the number of records to be shown (see Picture), it can be done with the following Array formula

=IFERROR(INDEX($B$4:$B$500,SMALL(IF($D$1=$A$4:$A$500,ROW($A$4:$A$500)-ROW($A$4)+1),IF(ROW(1:1)<=$G$1,ROW(1:1),""))),"")

Please Keep in mind to enter the formula pressing CTRL+ALT+ENTER

enter image description here