Excel Magic Trick 341 Extract Data w OFFSET & INDEX function

[zaance26]

When i retrieved the data....and close the file..then all the data were showing value

..it will not remain linked.

[19592Crissy]

Hi, I am not sure if this is what I need to help me with my excel problem, I have to show that all (for this shall we say people) who live in a house with the an odd number such as 1, 3, etc will be entered into a prize draw. it has to display their name which is adjacent to the house number. Could you please help as I have been looking for this all day and am desperate now. Your videos are so easy to follow and very interesting but for today I need a specific help. thank you in advance C

[ExcelIsFun]

THE best place to ask complicated Excel questions is here:

mrexcel[dot]com/forum

Then send me the link.

The mrexcel message board allows easy communication back and forth for Excel questions.

Send the link to:

excelisfun at gmail

[aswilldf]

hey thank you so much, i ended up using this formula!!!! I just having problems keeping the formats when the info is indexed. For instance for your table on the left, all the date for "fundd2" is being displayed in red, is there a way you can get it for that to tranfer over on "select fund", so that when you pick "fund2" it will also be displayed in red.

[aswilldf]

@aswilldf Or lets say in "fund2" , d19(data597) had a link, is it possible for that two transfer as well? if not possible, still thank you for everything you do for us less skilled!

[ExcelIsFun]

Try this video for conditional formatting (there are many tricks in this video, you wnat the one that allows you to highlight a row):

Highline Excel Class 21: Conditional Formatting 12 Examples

[rameshlakavat]

Awesome technique...

I have a quick question though- Can we make the height and Width dynamic, since my data is not always 6 row tall and 3 column width.

Thank you

Ramesh

[ExcelIsFun]

Yes, but it depends on how the data is set up. Is the criteria in the first column? Does the criteris repaet in the column? Is the data set sorted. Other important data set up paramenters...?

[ExcelIsFun]

Using the data set as seen at the 20 second mark (you should download the workbook and look at this), this formula would extract variable height:

=IF(ROWS(G$32:G32)>COUNTIF($B$­31:$B$48,$G$31),"",INDEX(C$31:­C$48,SMALL(IF($B$31:$B$48=$G$3­1,ROW($B$31:$B$48)-ROW($B$31)+­1),ROWS(G$32:G32))))

Entered with Ctrl + Shift + Enter (not just Enter).

[ExcelIsFun]

Here is a formula on that same data set (20 second mark) that does both variable height and width:

=IF(OR(COLUMNS($G32:G32)>COLUM­NS($C$31:$E$31),ROWS(G$32:G32)­>COUNTIF($B$31:$B$48,$G$31)),"­",INDEX(C$31:C$48,SMALL(IF($B$­31:$B$48=$G$31,ROW($B$31:$B$48­)-ROW($B$31)+1),ROWS(G$32:G32)­)))

Entered with Ctrl + Shift + Enter (not just Enter).

[aswilldf]

thank yo so much!!! i need more help

[ExcelIsFun]

You are welcome!

What more help?

[thuynguyenable]

Dear ExcellsFun,

Can you talk a bit more about the conditional formatting in this video? The red color jumps to different references.....

[ExcelIsFun]

Sure! See this video:

Excel Magic Trick 544: Conditional Formatting Row With Multiple Criteria In Other Columns

[wjj4832]

Awesome video - I can use that trick! Do you experience a serious slowdown when updating chart data using a method like this? I turn off calculations (set to manual) and turn off screen updating, but it's slow as molasses, whereas Excel 2000 is blindingly fast. Excel 2007 is taking about .4 seconds, where Excel 2000 works in near real time.

[ExcelIsFun]

Dear wjj4832,

The OFFSET is volatile, which means if you recalculate, it will recalculate even if the data has not changed. Use the First formula that I showed in the video if you do not want to use a volatile function.

If Excel 2007 is slow, it maybe because of RAM size. Below 2 gigs. Causes slowness.

--excelisfun

[planiolro]

This is the first time when I see this syntax in your videos (maybe I have missed some of your videos). However this is a very interesting idea have seen this on Mr. Excel but still I am not sure what kind of functions support this kind of syntax or when it is recommended to use this "syntax". Good video as always.

[ExcelIsFun]

Dear planiolro,

I am glad that you liked the video!

--excelisfun

[PrincessWithSkills]

Awesome!

[ExcelIsFun]

Dear PrincessWithSkills,

Coming from a Smart Lady like you, that's a pretty good compliment! Thanks!

--excelisfun