View Full Version : show source of value

07-29-2009, 12:53 AM
Hi all,
how can i do following:
I have workbook with 2 sheets. First sheet is source table with filter and in second is „info table“ which use data from sheet 1. In this info table i use formula - sumproducts for displaying infos what i want from source.

What i try/want is, how can i do something, what will after clicking on amount in infotable activate source table and set the data, that it shows the details of this value.
I try it say more simply. After clicking on value in info table, shows counted/used data for this value. Not whole source table, but only filtered (which was used/counted) data.

Is it possible?

Bob Phillips
07-29-2009, 01:08 AM
If your sumproducts are using cells to hold the tested value, such =SUMPRODUCT(--('Info Table'!A1:A100=A1), --('Info Table'!B1:B100=B1)) then you could filter Info table and with a button set the filter values to the tested values.

07-29-2009, 01:44 AM
I dont know if i understand you.
I add an example file.
I need something, what after one action (f.e. click on value like hypertext) will shows in source exactly relevant data for this value
as in atach. If i click on green value, it will show table which will be filtered as is it now in atached file. Do you know what i mean?

Bob Phillips
07-29-2009, 02:06 AM
I was suggesting that you filter by the ame criteria as the SPs, but I do not see where 14, 26 and 31 come from in your SP formulae. Joe has 26 in the first SP, black has 31, why?

07-29-2009, 02:42 AM
Those are only fake data. But my real data seems something like my example.
I dont know what you mean with SP. Is it sheet?
It Works this way. Source table i fill with vlookup from other table (6000 rows and 100 columns) and then change it on values. This my source is in real table about 1500 rows and 10 columns. From this source table i create sumarily tables with sumproduct. I use sumproduct because it is easier as everyday create infotable from source using autofilters and doing many selection (in filters). I dont know if i am clear.
So the data in source are fixed and i select only a few criteria to fill my info table. And this "info table" is in other sheet.

Bob Phillips
07-29-2009, 03:51 AM

Yes, that is clear, but what is not clear to me is where 14, 26 and 31 comes from and how you know where to use them. It seems a tad hahazard to my eyes.

07-29-2009, 04:04 AM
finaly i understund my misteak. I can add the next atach so i write it.
SP forumla in column B is in whole column the same. There is in each SP in this column this part --(source!$C$2:$C$20=14). My mysteak was, that there was other number as 14. :(

Bob Phillips
07-29-2009, 04:38 AM
Uh? Can you pass that by me again? Is your question resolved now, and if so, can you explain to me how?

07-29-2009, 05:03 AM
No it is not solved.
Now it is only explained that why was there diferent numbers.
You ask me:
"but what is not clear to me is where 14, 26 and 31 comes from"
and now you know, that there can be only number 14 in column...

But still i dont know, how can i display by one action/click data, which are counted with SPs.
I need something, what will set autofilter in source table, that it will apper in sum rows the same number as it is in "info table". One of column in source is count (in this column are only number 1 - in head of table i have sum function which count all 1s in this column - if i applie all filters as they are set in SP in info table the result will the same. And this i want. By one click set this filter, that sum will be the same.
I know, that my explanation are terrible :( sorry.