Display Sheet1: (A1:A10) value in Sheet2: (B1)

sam

unread,

Aug 26, 2009, 7:54:01 PM8/26/09

to

Hi All,

I have a sheet1 where data is diaplayed in column A based on a formula, Now
the data can be displayed anywhere between cell A1 and A10,
What I want to do is: I want to display this value from Sheet1(A1:A10) in
Sheet2 Cell B1

So basically it is a range of cells on sheet1 in column A that the value can
be displayed(it is random) and I want to display that value in Sheet2 Cell B1

Hope I made it clear

Thanks in Advance

Jacob Skaria

unread,

Aug 26, 2009, 9:07:01 PM8/26/09

to

'If you are looking for a formula; In Sheet2 Cell B1 place
=MAX(Sheet1!A1:A10)

'VBA
Sheets("Sheet2").Range("B1") =
Worksheetfunction.Max(Sheets("Sheet1").Range("A1:A10"))

If this post helps click Yes
---------------
Jacob Skaria

sam

unread,

Aug 26, 2009, 10:24:01 PM8/26/09

to

Hi Jacob, Thanks for your help.

Using max would work for numberic values rite? I am suppose to display a mix
of alplabets and numbers, something like C34, AI559 etc

So, this type of data would be displayed in any of the cells in
sheet1(A1:A10) and I have to display that in Sheet2(CellB1)

Thanks in Advance

Jacob Skaria

unread,

Aug 27, 2009, 12:19:01 AM8/27/09

to

Apply this formula in Sheet2!B1
=INDEX(Sheet1!A1:A10,MATCH("*",Sheet1!A1:A10,0))

VBA
Range("Sheet2!B1") = _
WorksheetFunction.Index(Range("Sheet1!A1:A10"), _
Application.Match("*", Range("Sheet1!A1:A10"), 0))

sam

unread,

Aug 27, 2009, 12:38:01 AM8/27/09

to

Hey Jacob, I applied your formula to Sheet2!B1 But I am getting a #Name?
displayed in Cell B1. Am I missing something?

Thanks in advance

Jacob Skaria

unread,

Aug 27, 2009, 12:47:01 AM8/27/09

to

Try this in the same sheet and see what happens. It should return the 1st
entry in that range.
=INDEX(A1:A10,MATCH("*",A1:A10,0))

sam

unread,

Aug 27, 2009, 1:35:02 AM8/27/09

to

Yes, it does display the value when I put it in the same sheet. What should I
do to display the value in different sheet?

Jacob Skaria

unread,

Aug 27, 2009, 2:02:01 AM8/27/09

to

Your sheet names are having a space in between..In which case you need to
have a apostrophe as below..

=INDEX('Sheet 1'!A1:A10,MATCH("*",'Sheet 1'!A1:A10,0))

sam

unread,

Aug 27, 2009, 10:24:11 AM8/27/09

to

That worked out great! Yea I had space in between.

Thanks

sam

unread,

Aug 27, 2009, 10:45:01 AM8/27/09

to

Hey Jacob, It did display the value first time, But now its not displaying
the value. I tried clearing the columns(A1:A10) and reopening the workbook,
But it still doesnt display the value.

For eg, I got the value the first time, then I cleared the colulmns A1:A10
and did the process again, but now the value is not displayed in Sheet 2 : B1

Hope I made it clear,

sam

unread,

Aug 27, 2009, 10:52:13 AM8/27/09

to

The issue is: The value is displayed the first time, If I get the same value
then its displayed, But If i get a different value then it is not displayed.

For eg, First Time I got a Value on 1A2 which was displayed
second time I got a value of 7E9 which was not displayed
third time I got a value of 1A2 again and then it was displayed again.

The value in sheet 2 Cell B1 does get cleared once I clear the contents in
Sheet1 Cell A1:A10, But it wont display a different value for some reason.

sam

unread,

Aug 28, 2009, 1:21:01 PM8/28/09

to

Hey Jacob,

Can you please help me with this? I tried other ways to get this to work
but it doesnt work. I think VBA would be a better choice to work this out.

I tried this in VBA, But it doesnt work.

Range("Sheet2!B1") = WorksheetFunction.Index(Range("Sheet1!A1:A10"), _

Application.Match("*", Range("Sheet1!A1:A10"), 0))

Error I get:

Method "Range" or Object "_Worksheet" Failed