Escelfilter: Making a dummy worksheet to simplify display of information?
February 2, 2010 8:24 AM   Subscribe

I have a massive spreadsheet that's simply too difficult to read, because of its size. Is there any way of making a second spreadsheet that is exactly the same as the first, except that the 2nd's cells just show an "x" if there is information in the 1st's cells. i.e., the 2nd spreadsheet would just show whether or not the cells on the 1st spreadsheet have been filled in, but not the actual information itself. If sheet 1 looks like this, sheet two should look like this.

Sorry if the question isn't clear. A second, important detail is that both pages should be sortable (by column a, ascending, or whatever) without affecting the order of the other sheet (I'm not sure that's an issue, anyway).

"Always link this cell (from worksheet 2) to box A1 (from worksheet 1). If A1 has content, display 'x'"
"Always link this cell (from worksheet 2) to box A2(from worksheet 1). If A2 has content, display 'x'"

And so on.

Mac, Excel 2004 v.11.5.5

THANK YOU!
posted by omnigut to Computers & Internet (15 answers total)
 
Response by poster: Oops: "Excel-fi"! Crap!
posted by omnigut at 8:25 AM on February 2, 2010


Best answer: Yeah, you need to fool around with IF statements and absolute links. Something like this:

=IF(Sheet1!A1<>0,"x"," ")

(if in Sheet 1, cell A1 is not equal to zero, display "x", otherwise display " "

You can then get into conditional formatting to change the colors of those cells with data & those without.
posted by MesoFilter at 8:36 AM on February 2, 2010


There's probably a logical test for "is blank?" but I don't know what it is.
posted by MesoFilter at 8:38 AM on February 2, 2010


Best answer: How I've done similar things is this:

1. Assume your data is in sheet 1 & sheet 2 will be the dummy worksheet
2. In sheet 2 cell A1 enter this formula: =IF(Sheet1!A1<>"","x","")
3. Copy/paste this formula to every cell in sheet 2 (you actually can't do it in *every* cell but do it in a large enough area to cover any possible changes/additions to sheet 1)

There is a flaw in this scheme, in that if you cut/copy/paste info around in sheet 1 it also affects sheet 2 in not-good ways.

To prevent that, lock all cells in sheet 2 (select all/format cells/protection/locked then review/protect sheet). (Or you can simply repeat steps 1-3 again whenever you need to regenerate the dummy table.)

FYI specific directions/menu items above are from Excel 2007 but all versions of Excel can do the same job--you'll just have to search the menu items to find the similar settings.
posted by flug at 8:46 AM on February 2, 2010


you can do =IF(Worksheet!A1="","", "x") for a "is blank?" (meaning, you can compare it to the empty string).
posted by jeb at 8:47 AM on February 2, 2010 [1 favorite]


Response by poster: Would using the $ symbol help stop prooblems with cut, copy and pasting in the other sheet? i.e.:

=IF(Sheet1!$A$1<>"","x","")

(Sorry, I'm not at my desk at the moment, so can't test this out - but will, later).

Also, this will show my newbieness, but on sheet two, cell A1, if I put this:

=IF(Sheet1!A1<>"","x","")

To copy that into all the cells below it, do I hover the cursor over the bottom-right hand corner of the cell, then click and drag that downwards? Because I think that will just copy the code exactly, instead of turning A1 into B1, C1, D1 etc etc. And if I remember right, you can only do that in a straight line (to cover either a column or a row), and not to cover an entire block. Right?


Thanks Again!
posted by omnigut at 8:59 AM on February 2, 2010


Looks like flug's actually done this before - his answer looks just about right.
posted by MesoFilter at 8:59 AM on February 2, 2010


The $ symbol will probably protect you from that problem, but when you copy/paste that into multiple cells, the absolute reference $a$1 will stay, so unlike regular Excel Copy/Paste, you won't get $a$2 when you paste it into the next cell, you'll still have $a$1.
posted by MesoFilter at 9:03 AM on February 2, 2010


So, you'd have to edit each cell in Sheet2 individually to $a$1, $a$2, $b$1, etc., and who wants to do that?
posted by MesoFilter at 9:04 AM on February 2, 2010


Using $A$1 won't solve your problem, and it will give you a new one: you WONT be able to paste/drag the formula all over, you'll have to put it in correctly for every cell. I'd go with flug's strategy.

if you do Sheet1!A1 and drag it from the corner, it will do B1, C1, etc. In fact, if you just copy and paste, it will do the same. So in Sheet2 A1, just put =IF(Sheet1!A1<>"", "x", "") or whatever your exact formula is select the cell, copy it, then do cmd-a to select all, then cmd-v to paste.
posted by jeb at 9:05 AM on February 2, 2010


To copy that into all the cells below it, do I hover the cursor over the bottom-right hand corner of the cell, then click and drag that downwards?

I'm, not sure how a mac differs from a PC but you can copy the cell & the drag an area and then paste the cell.

Be sure to copy the cell & not the formula (from the formula bar). If you copy the formula, then you paste the formula & won't get the relative references to change.
posted by MesoFilter at 9:05 AM on February 2, 2010


Response by poster: Wow, yeah - the spreadsheet is a 26 X 164 grid...I don't think writing that out would be possible without a huge dose of adderall. I'll try to work out a search-and-replace thing to enter the $ symbols after I've copy and pasted.

People, thanks again, that was really helpful and quick!
posted by omnigut at 9:07 AM on February 2, 2010


Wow, yeah - the spreadsheet is a 26 X 164 grid...I don't think writing that out would be possible without a huge dose of adderall. I'll try to work out a search-and-replace thing to enter the $ symbols after I've copy and pasted.

I'm not sure how you would accomplish this with a search & replace. You can't do a search & replace on formulas like that & you can't copy/paste the formulas for multiple cells into a text editor to get a good search & replace going. (Wouldn't it be nice if you could, though.)
posted by MesoFilter at 9:11 AM on February 2, 2010


Response by poster: Okay, it seems to be working!
posted by omnigut at 9:14 AM on February 2, 2010


I'm very confused as to what you're trying to do - not in terms of the x, but the end goal. Do you know about autofilter (data | filter > autofilter)? Conditional formatting?
Assuming your information is by row & column, you can filter out all the rows in the column that have information in them, leaving the blank rows. With conditional formatting you can make the blanks filled with a different colour, allowing you to spot them easily.
posted by defcom1 at 2:15 PM on February 2, 2010


« Older Using Dreamweaver to its least potential...   |   Hot or Not for project proposals Newer »
This thread is closed to new comments.