Using Reference Another Sheet from multiple cells

hasan.t

Hi Community,

I am working on a workspace that used for following weekly cases of the team. I have a sheet includes total case numbers for each week in one line, so there are 52 cells in total. All the cells works with "sum" function. There are 3 worksheets like this for each case types. So I have three lines from three different worksheets like cell D1 to D52

I want to see this numbers on another sheet on one column and the numbers have to be updated when the first sheets are updated. I'm using "value" function and select "reference another sheet" to choose the appropriate cell in the first sheet.

But I cannot make it collectively. I need to make the same thing to all cells one by one. Is there another way to make it quickly if I'm using reference another sheet.

Thanks in advance.

Jason Tarpinian

Could you make cell links to your target instead of a formula? If all of your fields are lined up the same on the source/target, you can select the entire row and link in all 52 values at once.

jessica.smith

@hasan.t Try using cross-sheet references instead of cell links.

For example, let's say you have 3 sheets (1 per case type) that each have a column to collect Week and a column to collect Number of Cases

You would set up 6 cross-sheet references (1 per column per sheet): {Type1_Week}, {Type1_Case}, {Type2_Week}, {Type2_Case}, {Type3_Week}, {Type3_Case}

Then on your Total Case sheet, you would use an index/collect formula to consolidate and sum all of the information.

Let's say on your Total Cases sheet you have a Week column (1-52) and a Total Cases column. In the total cases column, your formula would look something like:

=IFERROR(INDEX(COLLECT({Type1_Cases}, {Type1_Week}, Week@row), 1) + INDEX(COLLECT({Type2_Cases}, {Type2_Week}, Week@row), 1) + INDEX(COLLECT({Type3_Cases}, {Type3_Week}, Week@row), 1), "")

jdunn

so the way to do this is to first create a "Week" column (with numbers 1-52) on each of your summation sheets. Then, make a named range for each of the 3 sheets in entirety. then use this formula on each line: =sum(vlookup())

=Sum(VLOOKUP([week Number], {Reference to sheet 1}, number of your summation column, FALSE),
VLOOKUP([week Number], {Reference to sheet 2}, number of your summation column, FALSE),
VLOOKUP([week Number], {Reference to sheet 3}, number of your summation column, FALSE))

Please note that the week number column has to be the first column of your reference.

jmyzk_cloudsmart_jp

Hi @hasan.t

As Jason suggested, you can use the cell link if your summary sheet has the same orientation for Weeks 1 to 52 as sheets 1 to 3.

https://app.smartsheet.com/b/publish?EQBCT=66dca37a0b6e42ad920cf98f0235c4da

If your summary sheet transposes Week 1 to 52 vertically, you can use formulas to get values from the source sheet or sheets 1 to 3.

https://app.smartsheet.com/b/publish?EQBCT=c2c9f9083fba40ee8b6cc80f7321775d

The formula shown in the image above is as follows;

Memo 1
=COUNTIFS({Case Data Range : Case}, "Type A", {Case Data Range : Week #}, Week@row)
=VLOOKUP("Type A", {Sheet 1: Case Type A : Case Week 1 to 52}, Week@row + 1)
=INDEX({Sheet 1: Case Type A : Week 1 to 52}, 1, Week@row)
Memo 2
=COUNTIFS({Case Data Range : Week #}, Week@row, {Case Data Range : Date}, WEEKNUMBER(@cell) = Week@row)

If your original or source sheet does not have the Week # column and values, use the WEEKNUMBER function as Memo 2's formula.

Sample Source Sheet

https://app.smartsheet.com/b/publish?EQBCT=0ef64330056f4aa0b05e9f357f5d8f2e

Sample Sheet 1

hasan.t

https://community.smartsheet.com/discussion/comment/466949#Comment_466949

Hi Jason,

It sounds good, thank you. But in the second sheet, my data will be on one column, so I need to transpose it. As I see, transpose is not an option on Smartsheet. Do you have any advice?

Georgie

Hi @hasan.t,

Just to let you know that several comments on this thread reached our moderation queue and were therefore not immediately added to the thread - I have now approved these comments and wanted to let you know they're now visible on the thread in case they answer your question (since you won't be notified as they are listed in the thread prior to your most recent comment).

If they don't answer your question, you can click "No" next to "Did this answer the question?" on every answer so that we know that you still need help 😊

Thanks,

Georgie

hasan.t

hi @Georgie

Thank you for informing me. 🙂 I will check the answers. we decided to take this job on hold and I will not work it nowadays. I am not sure if the answers work so, you can close this question for now if you would like. If possible, I can ping the owners of the answers for my further questions.

Thanks,

Hasan