Excel question

[VJ]

Hello,

A friend of mine has a relatively simple looking excel question, but I don't see how to start working on it.
The document has multiple worksheets. On the first sheet is a table:

name | 1 | 2 | 3 |  ...
A     | x |   |   |
B     |   | x |   |

The other sheets also contain tables. The idea is that if you put an x in one of the numbered columns, the name is placed the tabel on the other sheet. So in the above example, the table on the second sheet would contain A, whereas the table on the third sheet would contain B.

Any ideas on how to do this?

Thanks!


Jörg

[Fat Tone]

You could populate the cells in the tables on sheets two and three with Vlookup functions whose named ranges include reference to the first sheet.

[VJ]

It seems that vlookup is only capable of looking in the first column...
(and it cannot work between worksheets, but that is easily circumvented)

Jörg

[Gurm]

Well there's the French Engineering Method (Blunt Instruments, Bad Judgement):

Set up a scratch area one column wide, filled with relative IF calls, like "IF Sheet1!E3 = 'x' THEN Sheet1!A3".

Then for the table on sheet3, do a concatenation of that entire column, which will drill it down to a neater form.

I'm sure there's a more elegant solution, though.

[Fat Tone]

Have you tried nested IFs?

[VJ]

I wouldn't know how to start...
The table on the first sheet has 30 columns to put x's (implying that there are 30 worksheets!) and has about 200 lines.
In a database, this would be easy... Perhaps I should just make a connection with access (first worksheet populates the database, other sheets query it)?


Jörg

[VJ]

I now have something that appears to work with relative simple IFs; in each cell where a field should be put on the worksheets, I have the following:

IF(cell in worksheet 1 = x, cell that needs to be put here, "")

On each worksheet, this presents me a list with the data I need. BUT there are blank lines... Any quick way to remove blank lines? (I could add a filter, but I would like something more definite that that... can one apply a filter on one column and have te results copiedin another?

Thanks!

Jörg

[Fat Tone]

When I've done that sort of thing (e.g. for filtering outliers +/- xSD from a dataset) I've used macros. IIRC I looped down the data and copied to a new column if it passed the criteria. Just keep track separately of the source and destination row indices. If you are moving to macros then you could use them to do the whole thing.

[Umfriend]

Hello,

A friend of mine has a relatively simple looking excel question, but I don't see how to start working on it.
The document has multiple worksheets. On the first sheet is a table:

name | 1 | 2 | 3 |  ...
A     | x |   |   |
B     |   | x |   |

The other sheets also contain tables. The idea is that if you put an x in one of the numbered columns, the name is placed the tabel on the other sheet. So in the above example, the table on the second sheet would contain A, whereas the table on the third sheet would contain B.

Does the second sheet "know" it needs to look for an "x" in the second column (column "1")? If so, then it can be done. If, for instance, all the other sheets have the column in Cell A1 you could use:

=INDEX(Sheet1!A2:A4,MATCH("X",OFFSET(Sheet1!A2,0,A 1,3,1),0))

The Offset function gives you the column number in which you will check for an "X". Note the fourth parameter which should be the number of (data) rows in your table on the first sheet.

The Match function then returns the actual row number in that column where an "X" is found

The Index uses that row number to look in the first column of the first sheet for the name.

Give it a go.

Edit: aside from using UDFs, I've found no function that returns the number of a given sheet so as to use as a pointer to the right column. Also, sheet numbering in Excel/VBA is not known to me as being completely predictable. I think, therefore, that it should be expected to have a cell on each sheet to provide some sort of identifier for the appropriate column.

[VJ]

I think I understand where you are going, but I'm struggling with the offset function, the third parameter in particular... why do you put A1 there (it seems to want a number as third parameter)?

Jörg

[Umfriend]

Well, you have all these sheets, right? And each sheet needs to read out of a certain column on Sheet1, right? You need to know on each sheet which column from Sheet1 it needs to read. That columnnumber, I proposed, would be stored in cell A1 for each of these sheets.
So on Sheet2, you would have "1" in cell A1 and the formula I gave you wherever you want. On Sheet3 you'd have "2" in cell A1 etc. etc.

The offset function would for its third parameter look at the value in cell A1 on the sheet the formula is used (it does not need a hard number, it needs either a hard number or a reference to a cell that contains a number).

[VJ]

You lost me again...
The table on the first sheet looks like this

   | A | B | C | D | E | F | G | H
---+---+---+---+---+---+---+---+---
 1 | a1| b1| c1| d1| x |   | x |
 2 | a2| b2| c2| d2|   | x |   | x
 3 | a3| b3| c3| d3| x | x |   |

An x in column E always refers to sheet2, one in column F always to sheet3, etc...
In this example, sheet2 ought to look like this:

   | A | B | C | D | ... 
---+---+---+---+---+----
 1 | a1| b1| c1| d1| ...
 2 | a3| b3| c3| d3| ...

Sheet3 like this:

   | A | B | C | D | ...
---+---+---+---+---+---
 1 | a2| b2| c2| d2| ...
 2 | a3| b3| c3| d3| ...

Sheet4 like this:

   | A | B | C | D | ...
---+---+---+---+---+---
 1 | a1| b1| c1| d1| ...

Jörg

[Umfriend]

Ah, I had not realised each sheet might need to populate more than one row. Yes, this can be done as well, even without showing blanc rows but it does get more complicated. You'd need an (hidden) area for some prepping work (finding pointers to columns and rows) but essentially the formula I gave would do the trick when adjusted for the prepped paramters in the (hidden) area. You'd still need to tell SheetX from which column it should read though. I would consider doing this in VBA (but you'd lose compatability with MacOffice 2008).

Have you found a workable solution yet? I could look if I can find some time. Should not take more than 30 minutes I think.

[VJ]

No, I haven't found something that works yet...
I would be very grateful if you could find time...


Jörg

[Umfriend]

I'll PM you my e-mail addy. Pls send the workbook to me. Any deadlines? I am a bit pressed atm but if you can wait till friday next week I'm sure I'll be able to meet the target.