Excel Magic Trick # 273: Extract Top 5 Records w Formula

[chaboya353]

hi

how can i make repeated rows without changing e.g A1,B2 as these values consider as input user, as we know that making multiple rows the value change e.g the A1,B2 will be as foloows

A2,B3

A3,B4 and so on

thanks

[ExcelIsFun]

I do not know. Try THE best Excel question site:

mrexcel[dot]com/forum

[chinchonchinchon]

Just like planioro I had to watch it several times to understand this type of "mixing" rows and columns, although I didn't watch this one: I watched another very similar to this one and I tried and did finish this one by myself!

The tricky part is to understand where to use rows and columns. I was dragging to the right along the columns with the rows function, naturally it didn't work, but finally It downed on me that it had to be COLUMNS!

This is amazing!

[ExcelIsFun]

It is amazing, chinchonchinchon, how far you have come with your Excel skills in such a short time!

Yes, you are right about the confusion of when to use the COLUMNS or ROWS function. I still mix it up sometimes...

[chinchonchinchon]

@ExcelIsFun

I had to struggle for a couple of hours before finishing the formula though.

I can understand the formula, but if you ask me to come up with such a clever formula I'd be still scratching my head trying to figure it out. Aladinn is indeed a clever fellow.

[ExcelIsFun]

Yes, but if you can understand it and you know where to go and ask, that is 99% of the battle.

Plus, if you keep at it, it will be amazing how some of the new knowledge can be applied when a new problem comes up!

[planiolro]

This is a awesome video with an amazing usage of different excel functions. I have to watch it several times in order to fully understand it. Some time ago u mention something about a play list for indirect function.

[ExcelIsFun]

Dear planiolro ,

Yes, I just posted it recently. Go to the Playlist and near the bottom is the new INDIRECT function playlist.

--excelisfun