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

'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" wrote:

> 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

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" wrote:

> '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" wrote:
>
> > 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

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))


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


"sam" wrote:

> 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" wrote:
>
> > '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" wrote:
> >
> > > 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

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" wrote:

> 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))
>
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "sam" wrote:
>
> > 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" wrote:
> >
> > > '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" wrote:
> > >
> > > > 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

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))

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


"sam" wrote:

> 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" wrote:
>
> > 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))
> >
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "sam" wrote:
> >
> > > 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" wrote:
> > >
> > > > '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" wrote:
> > > >
> > > > > 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

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?

Thanks in advance

"Jacob Skaria" wrote:

> 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))
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "sam" wrote:
>
> > 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" wrote:
> >
> > > 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))
> > >
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "sam" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > '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" wrote:
> > > > >
> > > > > > 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

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))

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


"sam" wrote:

> 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?
>
> Thanks in advance
>
> "Jacob Skaria" wrote:
>
> > 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))
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "sam" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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))
> > > >
> > > >
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "sam" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > '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" wrote:
> > > > > >
> > > > > > > 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

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

Thanks

"Jacob Skaria" wrote:

> 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))
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "sam" wrote:
>
> > 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?
> >
> > Thanks in advance
> >
> > "Jacob Skaria" wrote:
> >
> > > 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))
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "sam" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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))
> > > > >
> > > > >
> > > > > If this post helps click Yes
> > > > > ---------------
> > > > > Jacob Skaria
> > > > >
> > > > >
> > > > > "sam" wrote:
> > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > '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" wrote:
> > > > > > >
> > > > > > > > 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

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,

Thanks in advance

"Jacob Skaria" wrote:

> 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))
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "sam" wrote:
>
> > 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?
> >
> > Thanks in advance
> >
> > "Jacob Skaria" wrote:
> >
> > > 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))
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "sam" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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))
> > > > >
> > > > >
> > > > > If this post helps click Yes
> > > > > ---------------
> > > > > Jacob Skaria
> > > > >
> > > > >
> > > > > "sam" wrote:
> > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > '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" wrote:
> > > > > > >
> > > > > > > > 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