| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
'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:A 10")) 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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:A 10")) 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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. "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:A 10")) 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Display Sheet1: (A1:A10) value in Sheet2: (B1)
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 "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:A 10")) 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| display a value from Sheet1-A1:A10 in Sheet2-B1 | Excel Worksheet Functions | |||
| Click one cell sheet1, display all related recs on sheet2 | Excel Worksheet Functions | |||
| A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated | Excel Programming | |||
| Display Rows From Sheet1 In Sheet2 (Import) | Excel Worksheet Functions | |||
| Combine 3 Columns on Sheet2 to Display in 1 Column on Sheet1 | Excel Worksheet Functions | |||
Linear Mode
