Random Tech Questions you want answered.

I was worried it might also have lots of pictures or sound files that might have aided fragmentation, but I think it's just my anxiety lol. Thanks for the info, Rykin.

This video gives a decent overview on how fragmentation happens:

Thanks Rykin!

Also keep in mind that fragmented media files normally play just fine, because their bitrates are much lower than what the media can provide, even fragmented. Most spinning drives can deliver a hundred megs a second or more, and even high-res video files are normally only three or four megs a second.

Fragmentation could matter if you're trying to play multiple movies at once, but would probably almost never be visible with only one.

Good to know. My experience is on the server side, with lots of streams at once.

I'm looking at getting a new, larger media storage HDD. Probably 8-10TB. Is there a reason to go with 7200RPM over 5400RPM? It's typically being accessed by Plex.

The faster drives are only worth it if you're doing a lot of writes to the drive. For reading files, especially through Plex, you'd never see a noticeable difference between the two, unless you got a really sh*tty 5400 RPM drive and compared it to a beast of a 7200 RPM drive (think no-name China special 5400 vs a Seagate or Western Digital Enterprise 7200).

Yeah for a Plex library just get the 5400, it will probably be quieter and consume less power too.

Excel Gurus:

Working at a small hardware company we keep track of our customer returns with an excel spreadsheet where each row references a particular system and the columns in that row act as fields e.g. s/n , reported problem, date returned ect.. We manually transfer that row data over to a report excell sheet that is printed out and sent to the user. It is slow and laborious.

I am trying to find the best way to make a selected/highlighted row auto-populate a second sheet. My first thought was have a selected row auto populate sheet 2 row 1. Then having the report (sheet 3) auto populate with the information from sheet two, row 1.

Is there a forumula I can use to use to grab a particular cell in a highlighted row from another sheet?

Any suggestions are welcome. I'm even open to throwing out the whole Excel thing if their are better cheap/ free programs, or services, to track repairs and print reports.

Yes you can do this with the INDIRECT function.

For example say in your report sheet you have cells A1 though E1 that you want to populate with row 12 from the original returns list. You could have a 'row pointer' cell where you tell the sheet what row to pull, let's put it in cell P1 on your report sheet.

In P1 (your pointer cell) you put the row number you want to pull, in this case 12.

In A1 you put something like =INDIRECT("ReturnSheet!A"&$P$1)

ReturnSheet is the name of the sheet where the data comes from. The 'A' tells it to pull column A, $P$1 tells it to pull whatever row that the pointer cell has in it, in this case 12. So it factors out to pull cell A12 from ReturnSheet.

Copy that to the other fields you want to fill out, adjust column A to columns B, C, etc. And then going forward you just need to change your pointer cell (P1) to be whatever row you want to pull the return data from and it will automatically update your form.

There are probably free or low cost specific return tracking tools out there, but I don't know of any good ones off the top of my head.

Thanks for your assistance! I just tried it out and I'm doing something wrong.

Sheet1 , C98 = 10-3232 R200
(that is a part #)

Just to test it out on sheet2 i put in the value of 98 in P1.

Then put in the following formula in sheet2 , C6

=INDIRECT("Sheet1!C"&$P$1)

But it just shows the formula in the cell instead of a value. No errors or anything.

Edit: Weird but I paste that same formula in D6, a cell that haven't been messing, with and it works! Does that make any sense?

Edit 2: Doh, it was categorized as a "text" cell. Thank you!!!

Cool I am glad you got it working. Explaining excel solutions via text always makes it seems more complicated than it actually is.

Thank you Pur and Leaping for answering my question while I completely left this thread for 2 weeks.

LeapingGnome wrote:

Cool I am glad you got it working. Explaining excel solutions via text always makes it seems more complicated than it actually is. :)

No kidding. Searching excel solutions is super complicated when you don't know exactly what you are looking for too.

I'm smoothing out so much of the process now with pull down lists and auto fills!