1

New to Google sheets, so feel like I'm falling down somewhat of a rabbit hole trying to figure this out...

I have a spreadsheet 'sheet 1' where we keep the details of our sales - Name, address, design, size, tracking.
Then we have 'sheet 2' set up as a job requirement sheet, this gathers the data to be sent to the manufacturer - like a receipt. But, we only need certain info and only from one row at a time.

I can create the reference cell in sheet2, say I select cell F13 ( where I want my order number ) and type ='sheet 1'!A131, this gives me the order number from sheet1.
What I would like to do then, is have the other chosen cells change depending on that reference cell.
I have each required information cell with a series of these formulas; =OFFSET('sheet1'!A131, 0, 6, 1, 1), which works...but means every time I change the reference I need to update all of the formulas manually to match the original reference ( say A131 changes to A132 ).

I can't use =OFFSET(F13, 0, 6, 1, 1), for obvious reasons it doesn't take into account the formula address from 'sheet1'.

Is there a way to get this to work across 2 sheets? I haven't found a way to do it by searching Google, other than the below - but alas I can't understand how to get it to work, kept getting errors.

=OFFSET((INDIRECT(ADDRESS(1,1,,,"Sheet2")),ROW(),0,,)
Improve this question
1

1 Answer 1

Reset to default
1

One way to do this would be to replace the formula in cell F13 with a drop-down list. Set the cell range of the drop-down list to the column sheet1!A2:A. When you have inserted the drop-down list, choose one of the order numbers by clicking the down-arrow ▼ in the cell.

Then replace your offset() formulas with vlookup() formulas like this:

=iferror( vlookup($F$13, sheet1!$A$2:$Z, column(sheet1!$B:$B), false) )

At each instance, replace column(sheet1!$B:$B) with a reference to the column where you want to pull the data from.

Improve this answer
3
  • Hi doubleunary, thank you so much! This worked exactly how I needed.
    – Kimmie
    Oct 19, 2021 at 4:44
  • I have the sheet all gathering data as expected, could I possibly pick your brain about one small variation? The area I have for products sold works for one item only, as these will be multiple rows (same column), for example 'order 105' has customers details across one row with the first purchased item in that same row, second purchase drops to the cell below an so on. Is there a way to create a formula that would look at the first product but then offset down one row? Hope that makes sense. Thank you
    – Kimmie
    Oct 19, 2021 at 5:10
  • Please ask only one question per post.
    – doubleunary
    Oct 19, 2021 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.