Need help with Excel function or formula

Z

ZeroKool

Member
I have a table of lottery drawings in a row form per each drawing (say Pick-3). What I need is a function or formula that will allow me to copy any of the row from the drawing table to a reserve row CELL[B56,C56,D56] by specifying which row to copy in CELL[C52].

Example:
1) Last row of drawing table: CELL[B41,C41,D41] has a content of a type-number 8-5-6 respectively. The next drawing will be place at CELL[B42,C42,D42] and so on.

2) Let say, I want to choose row 41 to be copy (or any row from the drawing table) by specifying 41 into the content of a reserve cell CELL[C52].

3) The content of row(41) CELL[B41,C41,D41] is copy into row CELL[B56,C56,D56] respectively.

4) By changing the number (denote by x) in CELL[C52], The content of row-x CELL[Bx,Cx,Dx] is copy into CELL[B56,C56,D56] respectively.

Essentially the formula or function of row CELL[B56,C56,D56] is using the content of CELL[C52] to point to (or copy from) the content of row CELL[B41,C41,D41] respectively.

what I have tried, but result in error relating to data type (between type-number and type-text):
DRAWING ROW: B41=8,C41=5,D41=6
SELECTED ROW: C52=41
POINTER ROW: B53="B"&C52 ; C53="C"&C52 ; D53="D"&C52 [ <<== result in a text-type]
RESERVED ROW: B56=value("B"&C52) ; C56=value("C"&C52) ; D56=value("D"&C52) [ <<== result in data-type error]

Note: I think I need to convert the POINTER ROW data from text-to-number? how?

thanks in advance,
Zero
 
I

Icewynd

Member
Hi ZeroKool,

Not quite sure what you are trying to do here, however I can help with one problem.

To convert text to a number just multiply the cell contents by 1. =(x)*1

Good luck!
:thumb:
 
Frank

Frank

Member
I dont think you need pointer row at all, if you use the INDIRECT function


Try this:-

in cell B56 enter =INDIRECT("B"&$C$52)
in cell C56 enter =INDIRECT("C"&$C$52)
in cell D56 enter =INDIRECT("D"&$C$52)
 
Z

ZeroKool

Member
@Icewynd & Frank, thanks both of you for your help. Much appreciated.

@Icewynd
FYI, I've tried your method but resulted in data-type error. I am using MS Office Excel 2010.

@Frank
Your method worked perfectly for me. Couple more questions related to this function


1) How would you use the INDIRECT function call from a seperate worksheet (say from sheet2, drawing table is on sheet1)?
I have tried (on sheet2): B56=sheet1!indirect("B"&C52) [ <<== result in equation error]

2) How to copy a cell from sheet1 to sheet2 by incrementing the number in sheet2's cell content?
I have tried (on sheet2): A1=sheet1!indirect("B"&C(52+1)) [ <<== result in equation error, fail to add 1 or shift cell position to the next row]

thanks,
Zero
 
Frank

Frank

Member
ZeroKool said:
@Icewynd & Frank, thanks both of you for your help. Much appreciated.


1) How would you use the INDIRECT function call from a seperate worksheet (say from sheet2, drawing table is on sheet1)?
I have tried (on sheet2): B56=sheet1!indirect("B"&C52) [ <<== result in equation error]

2) How to copy a cell from sheet1 to sheet2 by incrementing the number in sheet2's cell content?
I have tried (on sheet2): A1=sheet1!indirect("B"&C(52+1)) [ <<== result in equation error, fail to add 1 or shift cell position to the next row]



thanks,
Zero
Click to expand...

1) Zerkool, youve made life difficult for yourself by trying to write formulas from sheet2 to relate to sheet1. The easy way is to stick to the INDIRECT formula(s) that work on sheet1. Then click on cell C52(on sheet 1) and cut and paste it to sheet 2. Excel will do all the work for you and rewrite the necessary formulas in sheet 1 to correct for sheet2. try it and see. I got =INDIRECT("B"&Sheet2!$B$4) for one because I pasted to cell B4 on sheet 2.

2)For the increment question:- In the example I'm giving you, I had pasted (what used to be cell C52 on sheet 1 to cell B4 on sheet2.

Wherever you wish the incremented answers to appear (say sheet1 cells B57, C57, D57 enter these formulas respectively:- (adjusted to where you pasted it)

=INDIRECT("B"&(Sheet2!$B$4+1))
=INDIRECT("C"&(Sheet2!$B$4+1))
=INDIRECT("D"&(Sheet2!$B$4+1))

You had a good try, but forgot that the bit after the & sign has to be a row number, and therefore you need brackets around the addition process so the answer is still a row number not a confused cell reference.

I hope this clears it up.
 
Z

ZeroKool

Member
Frank,
Your solution to both my question 1 and 2 is not what I expected. It will only look up the cell in sheet2 and not at the cell in sheet1 (the drawing table).

From your solution to question 1 & 2:
INDIRECT("B"&Sheet2!$B$4) , assuming the content of B4 on sheet2 is 41, then the result will be the content of cell B41 in sheet2. The same goes for question 2.

What I want is to reflect the result from the drawing table in sheet1 onto the reserve cells on sheet2.
Example:
DRAWING ROW (on sheet1): B41=8,C41=5,D41=6
SELECTED ROW (on sheet2): C1=41 [changable by user]
RESERVED ROW (on sheet2): B2=value of B41 on sheet1 ; C2=value of C41 on sheet1 ; D2=value of D41 on sheet1

Any change to the number (say x) of the SELECTED ROW will make B2,C2,D2 of sheet2 equal to the content of Bx,Cx,Dx of drawing table on sheet1.
I hope my explanation is clear.

Zero
 
Frank

Frank

Member
Okay, you didn't say upfront that you were intending to access the data from a different sheet. Had I known that I might have suggested just using the OFFSET function which gets around that in a much simpler way and you dont need row numbers, just where it is in the list.

However, we've come this far and you need to know that the INDIRECT function needs to know the sheet name it is reading from when its on a different sheet from the data. This means the formula has to have some pretty complicated concatenation included in order to tell it the sheet name it has to refer to.

As I understand it, you want BOTH the SELECTED ROW and the returned information (RESERVED ROW) to be on sheet 2, whilst the data from which the DRAWING ROW is chosen stays on Sheet 1. I notice you have moved the SELECTED ROW to from cell C52 to cell C1 on sheet 2.
I hope you have no more moving about of the goalposts to disclose to me after this amendment, as I shall rapidly lose interest.

Assuming the data is in the same row numbers as before on Sheet1, then:- Assuming your RESERVED ROW cells are B2,C2,D2 on sheet 2

in cell B2 type in :- =INDIRECT("'"&"Sheet1"&"'!"&"B"&$C$1)
in cell C2 type in:- =INDIRECT("'"&"Sheet1"&"'!"&"C"&$C$1)
in cell D2 type in:- =INDIRECT("'"&"Sheet1"&"'!"&"D"&$C$1)

(or copy and paste in)
Note that for clarity the first sequence is " ' " without the spaces
The sequence after the second & is " ' ! " without the spaces.

I hope this now does what you described. :)
 
You must log in or register to reply here.

Sidebar

Top