GOTO Result from Formula..?

[PM1]

=SUMPRODUCT(('2305'!$N$2:$N$3416>=100001)*('2305'!$N$2:$N$3416<=999999)*(LEFT('2305'!$B$2:$B$3416,2)="LN"))

The formula above produces a result of "1" (which is correct), however is there a way of adding something to the cell which allows me to go to the result from the source data, rather than have to filter out manually.

 

[Brian from Maui]

Care to elaborate?

 

[PM1]

OK, lets explain further: This is an extract from a Summary Sheet that I have:

Master Asset File.xls
	A	B	C	D	E	F	G	H	I	J
2			TotalAssets				LNONo
3			No	Value	%ofVal		Found	%	VALUE	%
4
5	InitialAssetRegister(2305)		3415	7,020,066			1754	51.4%	4,085,453	58.2%
6	InitialAssetRegister(2305)Software		33	0
7	InitialAssetRegister(2305)PhysicalAssets		3382	7,020,066
8		AssetsNBV=>0	2825	0			1306	46.2%	0	0.0%
9		AssetsNBV>0<25k	528	2,910,271	41.5%		406	76.9%	2,304,545	79.2%
10		AssetsNBV>25k<50k	34	1,143,418	16.3%		31	91.2%	1,015,469	88.8%
11		AssetsNBV>50k<100k	14	895,482	12.8%		10	71.4%	598,567	66.8%
12		AssetsNBV>100k	14	2,070,895	29.5%		1	7.1%	166,872	8.1%
13
Summary Sheet

This produces certain results from other sheets within the file. G12 shows a result of 1 which is arrived at from the formula give initially. What I want to be able to do is quickly go to that result rather than manually having to go to the sheet and filter out the relevant data to get the result that is reported in the table above.

So I was wondering is there some code I could use that enables me to goto the relevant data from the cell chosen.

 

[PM1]

Brian,

Was this explantion enough?

 

[Brian from Maui]

Brian,

Was this explantion enough?

I still don't understand, maybe someone else does....

Bumping!

 

[GlennUK]

When you say "manually having to go to the sheet and filter out the relevant data" ... how do you do that exactly? Are you asking us to tell you how to do that?

 

[PM1]

SUMPRODUCT(('2305'!$N$2:$N$3416>=100001)*('2305'!$N$2:$N$3416<=999999)*(LEFT('2305'!$B$2:$B$3416,2)="LN"))


produces a result of 1 in G12, to see this result I have to go to sheet '2305' and filter out all products that are greater than 100,000 and then filter Col B to show me the actual item that is reported.

What I want is a way to "Right Click" maybe and an option to go to this result from the cell chosen

 

[GlennUK]

If you are talking about using the Autofilter option on sheet '2305' to search for the value, then there is no other way, except for writing some VBA code which would do those steps for you ... navigate to sheet '2305' and choose 2 filter criteria.

 

[PM1]

Glenn,

I thought that, but to be honest the coding would be a huge challenge, especially when I have close to 256 cells with different results in.

I'll just have to get a quicker mouse.