I have a Table of Fixed values. Five Rows and Four Columns. I want to manually enter the co-ordinates of the first (LH) Table cell of a given row into a Pointer cell (W1) in my spreadsheet with the co-ordinates (X,1). Then I want Calc to automatically load all of the contents of the table row cells (left to right) (X1,X2,X3 & X4) into predetermined cell locations throughout my spreadsheet. Where the first predetermined cell is (A1) so Calc places the contents of (X1) into (A1). Then (X2) contents into (C1). (X3) contents into (E1). (X4) contents into (G1). What formula do I put into (A1, C1, E1 & G1)? Thank you.
I reopened the question, since there was no proper closure reason (duplicate question) and closure of questions isn’t required at all. Marking a correct answer as such is sufficient.
Thanks Opaque…this is my first time using this Forum and I’m still not sure how it works. I didn’t choose the “Duplicate Question” It came up Automatically when I hit “CLOSE” and I thought that maybe someone else had already asked this question and the Forum was telling me this. I also got a Bonus from your answer. The “Data” “Validity” “List” was new to me and I really like the drop down menus I am able to add to my Spreadsheets for Auto Fill from Data tables and the Error Comments. There is so much to learn about Calc. Thanks again.
Hello,
I’m really unsure whether I understood your undertaking, but may be this file using function INDIRECT() and OFFSET() is what you are looking for.
Usage: Insert the starting cell reference into cell B5, which I assume to be what you called W1 and which is pointing to starting row of the sub table. The green line shows, what is being selected. Row 2 shows the formula of the repective row above (i.e. B1 showing =OFFSET(INDIRECT($B$5),0,1) is the formula used in A1)
PS: My apologies in advance, if that isn’t what you have looked for.
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
Opaque,
No apology required. It works Great. I did try the “Indirect” function but I didn’t know about the “Offset”. So happy. Thanks for responding so quickly and the spreadsheet you produced made it happen. I don’t think I would have figured it out without that. Cheers LofA