Return a cell value for a row from another sheet

Brian Bulthuis

Hello SS Community!

Looking to get some help on a formula to return a cell value in a row on another sheet. The sheet below is the one I'm referencing. I'm attempting to retrieve the value in Column "BB" and for Row "Week 1".

I've figured how to count the cell by using COUNTIF (=COUNTIFS({week}, =Week@row, {BB}, <>0)), but of course that returns a "1" and I need it to return a "19".

I've attempted using SUMIFS, INDEX, IF, but nothing is working... Help!

Deric

The week column on the destination sheet needs to have the same value that you are matching on the source sheet. So if you followed the formula in my previous example, your matched value should read 2023 - Week 1 instead of Week 1.

Also, you may need to add ", 0" to the end of your match formula - depending on how your source sheet is set up. =INDEX({BB}, MATCH(Week@row, {year-week}, 0))

If those things don't work, then you likely have an issue with your cross sheet references.

Deric

There are several ways to do this depending on how you want to use the data.

You could 1) create a cell link by right clicking on the destination cell and choosing "link from cell in other sheet"; 2) create a column reference for BB and then use the index function referencing the row that the value is on, e.g., if "19" is on row 10: "=INDEX({BB}, 10)". These options are only useful if you only want to pull the one value or if you are ok doing this for every value you want to reference.

Using a helper column that identified the week and year would probably be the most useful - something like "=PARENT(Week@row) + " - " + Week@row". Then you could use index and match in your destination sheet to get the value from {BB}, e.g., =INDEX({BB}, MATCH("2023 - Week 1", {helper column}, 0)). The formula would be even more useful if you had a column in your destination sheet for all the weeks of the year, then you could use a column formula: =INDEX({BB}, MATCH([Weeks of the year column]@row, {helper column}, 0)).

Brian Bulthuis

Hello Deric,

This is very helpful! I've added the helper column and attempted to enter your formula for my destination sheet, but it's returning a "0" and not a "19". It seems as if I'm really close, but missing something simple here. Thank you so much!

Deric

The week column on the destination sheet needs to have the same value that you are matching on the source sheet. So if you followed the formula in my previous example, your matched value should read 2023 - Week 1 instead of Week 1.

Also, you may need to add ", 0" to the end of your match formula - depending on how your source sheet is set up. =INDEX({BB}, MATCH(Week@row, {year-week}, 0))

If those things don't work, then you likely have an issue with your cross sheet references.