Questions

Answer for:

I want to ask about a formula. Is any way to do?

Message 12 of 14

View entire thread
+
0 Votes
gechurch

Ok, thanks for the clarification. As I understand it you have just one row of data that you ever want to display/print at any time (and you are just putting it on a second sheet so it can be formatted for printing).

That's fine, and there are a few ways to handle it. For all examples lets assume Sheet1 is where your rows of data are stored, and Sheet2 is where you want to display the particular data. On Sheet1 lets say column B stores first name, column C stores surname, column D stores address and column E stores city.

Method 1) Have the user manually enter the row number to pull the data from on your sheet to be printed. ie. have a label in Sheet2!A1 that says "Row # to print" and have the user enter the row number into cell Sheet2!B1.

You would then use the INDEX formula. This formula takes in an array of data (in our example, this will be Sheet1!A1:E10), a row number (this will be that value typed in to Sheet2!A2) and a column #. On Sheet2 click on the cell where you want the first name to be displayed and enter the formula "=INDEX(Sheet1!$A$1:$E$10,$B$1,1)". That formula says "here's my array, now return me the value of column #1 in the row I specified in cell B1 on this sheet". Now click wherever you want the surname to be displayed and enter the formula "=INDEX(Sheet1!$A$1:$E$10,$B$1,2)". You'll notice that's the exact same formula, but the last value is a 2 now, meaning "grab the value of column #2 in the row I specified". Or in other words "return the value in the surname column". Continue on like this until you are pulling in every column of data.

Method 2) is a similar approach to above. This time though we are going to have the user type a 'p' in column #1 on Sheet1 for the row they want to print. We will then write a formula that looks for the row with a 'p' in column #1 and will read the values for each column in this row.

To do so, enter the following formula on Sheet2 in the cell where you want the first name to appear "=VLOOKUP("p",Sheet1!A$1:$E$10,2,FALSE)". That formula says to Excel "Look for the value 'p' in the first column of the table Sheet1!A1:E10. Once you've found the row with the 'p', return the value in column #2 of that row". Obviously to return the surname you'll change the formula to "=VLOOKUP("p",Sheet1!A$1:$E$10,3,FALSE)" (to get the value of column #3) and so on.


There are a few other strategies, but the ones I can think of are basically just variations of the above.