How to populate a summary page ????????????????????????????????????????

[Niek Otten]

Hi George,

Two options (at least)
Simplest: Tools>Options>View tab, uncheck Zero values. This will also make explicit zeros invisible. If you require only really
empty cells to show as zero, you'll have to change your formula. Unfortunately to a very long one.
I don't have the test sheet anymore, so I'll give you the recipe. If you can't get it done, post again and I'll try to reproduce
it.

In essence what you have to do is: change the part that pulls the data from the sheet :
INDEX(lookuptab,SMALL(IF(lookuptab=1,ROW(lookuptab)),ROW(1:1)),COLUMN(A1))
to
IF(INDEX(lookuptab,SMALL(IF(lookuptab=1,ROW(lookuptab)),ROW(1:1)),COLUMN(A1))="","",INDEX(lookuptab,SMALL(IF(lookuptab=1,ROW(lookuptab)),ROW(1:1)),COLUMN(A1)))
Sorry for any line-wraps
Be careful with brackets; the part to be replaced is just before the last closing bracket

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi, Niek:
|
| Everything is working fine. However, I do have another question.
| That's, if 1 is the input under column A in sheet 1, then this whole
| row with input 1 will be carried to sheet 2. The problem is if there
| are some empty cells associated with this row with input 1, I do NOT
| WANT a 0 showing up in sheet 2 in the corresponding cell. Could you
| please help me on this?
|
| Thank you so much!
|
| George
|
*******************************************************************************************************************************************************************************************
| Niek Otten wrote:
| > Glad you got it working, George.
| >
| > I had read Ashish's recipe before but this was the first time I applied it. So it was a useful exercise for me too!
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Hi, Niek:
| > |
| > | It is perfect. I do not know how many thanks I need to tell you. But, I
| > | will buy you a meal whenever you stop by Houston. E-mail me anytime and
| > | I will get in touch with you!
| > |
| > | Again, thank you so much for your help!
| > |
| > | George
| > |
| > | Niek Otten wrote:
| > | > <my own recipe >
| > | >
| > | > That's unfair. It is mostly Ashish Mathur's recipe
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | > | Hi George,
| > | > |
| > | > | <I apologize to give you a lot of troubles>
| > | > |
| > | > | Don't!
| > | > |
| > | > | I'll try again! I started with a blank workbook (except for the input table on Sheet1) and followed my own recipe and it
| > worked.
| > | > |
| > | > | Select A1:C4 on Sheet1.
| > | > | Define a name for this range, like LookupTab. Defining a name can be done via the Insert>Name>Define menu or by clicking
in
| > the
| > | > | Name box, that is the box to the left of the Formula bar.
| > | > | Select the following formula and copy it:
| > | > |
| > | > |
| > | >
| >
=IF(ISERROR(SMALL(IF(LookupTab=1,ROW(LookupTab)),ROW(1:1))),"",INDEX(LookupTab,SMALL(IF(LookupTab=1,ROW(LookupTab)),ROW(1:1)),COLUMN(A1)))
| > | > |
| > | > | Select A1 on Sheet2.
| > | > | Click in the formula bar and paste (do not press ENTER yet), then press-and-hold CTRL and SHIFT, press ENTER (you have
now 3
| > | > keys
| > | > | pressed) and release the 3 keys. The formula should now show between curly brackets in the formula bar.
| > | > |
| > | > | Now you can copy down as many rows as you like, and then copy right to columns B and C
| > | > |
| > | > | Later on you can change the definition of LookupTab to accommodate your input rows.
| > | > | --
| > | > | Kind regards,
| > | > |
| > | > | Niek Otten
| > | > | Microsoft MVP - Excel
| > | > |
| > | > || Hi, Niek:
| > | > ||
| > | > || I am sure it is a precise work. I am so sorry that I did not get it
| > | > || yet. Could you please show me the whole process in more detail
| > | > || including which keys to use? I apologize to give you a lot of troubles.
| > | > ||
| > | > || Thanks,
| > | > ||
| > | > || George
| > | > ||
| > | > ||
| > | > ||
| > | > |
| > | > |
| > |
|

 

[George]

Hi, Niek:

I really appreciate your time and efforts in helping me out. I know the
first option works. Also, the condition that "If you require only
really

empty cells to show as zero, you'll have to change your formula. Unfortunately to a very long one." does not exist. Thus we do not need to worry about this point. But I do not know how to make the second option work. Sometimes I am slow in keeping up with you, so please bear with me. If you would please show me in more details like the way you did last time, it will help me a lot!

Thanks again for your great patience!

George
********************************************************************************************************************

 

[George]

Hi, Niek:

Also, if I want to switch my input from Column A to a different column,
what should I do?

Thanks,

George

 

[George]

Hi, Niek:

After I apply the formula to my sheet 2 which has several hundred of
rows and 8 columns, it looks like it makes my file size super huge.
Therefore, whenever I want to change my input under Column A on sheet 1
even though this input is just one number, it will take a VERY LONG
TIME in "calculating cells" and this is not a good sign to me. Do we
have an alternative to achieve our goal? Please advise!

Thanks,

George
************************************************************************************************************************************************************