Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 (the raw data) if the first cell displays "x" value? What I am exporting is a daily list that contains a course number that I manually change into a home room number, name, grade, etc. What I then do, is sort by home room and display those in their own section on a work book.

So what I am trying to find out is something like "Display cells 2,3,4,5,6,7 of workbook two if workbook 2 cell 1 is "X" value. ?

share|improve this question

3 Answers 3

I’ll assume that your Workbook2 data start in row 1, and that you mean «Display cells from columns B, C, D, E, F, and G … if the value in column A is an “X”.»  In the first cell on Workbook1 where you want to start displaying data from Workbook2 (which will be from B1), type

=IF('path_to_Workbook2[filename_of_Workbook2, including .xlsx]Sheet1'!$A1="X",
    '
path_to_Workbook2[filename_of_Workbook2]Sheet1'!B1, "")

Then drag/fill that out to column F and down as many rows as you need.  Obviously, if your sheet is named something other than Sheet1, change that.

share|improve this answer

The following formula provides another way to display in Workbook1 the text in some row of six cells in Workbook2, if cell A1 in Workbook2 equals some value "X".

It is an array formula, so you would have to select the six cells in Workbook1 where you want to display the text values, paste or type the formula into the formula bar, and then complete the entry with Ctrl+Shift+Enter. The formula will show dashes ("-") in the cells if there is no "X" in cell A1 of Workbook2. (For simplicity, I assume the values in both workbooks are in Sheet1.)

  =IF([Workbook2.xlsx]Sheet1!$A$1="X",OFFSET([Workbook2.xlsx]Sheet1!$B$1,0,0,1,6),"-")

The key part of the formula is the OFFSET function, which takes the following arguments:

  • A cell reference, in this case $B$1
  • A row offset, the number of rows up (a negative number) or rows down (a positive number) that the range of cells you want to return begins
  • A column offset, the number of columns left (negative) or right (positive) that the range you want to return begins
  • The height, or number of rows in the range that you want to return
  • The width, or number of columns in the range that you want to return.

In the formula, the cell reference is $B$1, the row offset from $B$1 is 0, the column offset from $B$1 is 0, the height of the values to be returned is 1 row, and the width is 6 columns. Here, the OFFSET function will return the values in cells B1:G1 if "X" is a match. Had the row offset been 2 and the column offset 4, the values in cells F3:K3 would be returned.

share|improve this answer

How about VLookup?

Vlookup($A:$A,[OtherWorkbook.xls]OtherWorksheet!$A:$G,2,false)

share|improve this answer
1  
The style of your response is more suited for a comment, than an answer. Inquiries should be comments; answers are usually given in an assertive style. Thanks for participating! –  Nevin Williams Jun 17 '13 at 3:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.