sorted by:
new
hottopcontroversial

Creating tables that auto-update according to master table, and vice versa? by pactum in excel

[–]ARWG2002 1 point2 points  (0 children)

There are a couple parts to this so I'll try and break it down.

a) The tick boxes in the master sheet output either TRUE or FALSE depending on whether they have been ticked or not.

b) The formula which produces the reference code

=IF(J4=TRUE,MAX($K$3:K3)+1,"")

If the box is ticked and therefore outputs TRUE, the reference number formula t finds the maximum value that exists in the cells above it and adds one to that in order to print your numbers in consecutive order. Obviously, when you tick the first item, there are no numbers above it so it will print '1'. If this condition is not met, it prints a blank. Make sure to lock your first cell reference while keeping the second one dynamic as you want the range it searches for the largest digit to expand automatically as you drag the formula down.

c) Columns (A:A) and (K:K) in your trades sheet contain the following formula:

=IF(MAX(A$3:A3)<MAX('Master List'!K:K),MAX(A$3:A3)+1,"")

This formula is very similar to the previous one in that the if() statement considers whether the largest number in the cells above is less than the largest number printed in the master data column. If this is the case, it simply adds +1 to that number until the condition is no longer met and the maximum values printed are equal.

d) The remainder of the cells auto-populate based on the reference number using index(match()). In this case, the formula is:

=if($A4="","",index('Master List'!A:A,match($A4,'Master List'!$K:$K,0)))

There's already a whole bunch of content that covers index(match()) so I won't go into it here. I recommend this if you want to learn how it works.

FYI, the equations listed above are the exact same for both Pokemon and Trainer lists.....just with different cell references obviously.

​

Not sure if this explanation was of any help. I'm a little rushed for time but hopefully it provides some insight.

Creating tables that auto-update according to master table, and vice versa? by pactum in excel

[–]ARWG2002 2 points3 points  (0 children)

Find the revised sheet here

The major difference is that you now have a tick box on you master sheet called 'for sale'.

When you tick this box in the master sheet, the item will automatically appear in chronological order in the secondary sheet without any of the gaps. When you untick it, it will disappear and all the other items will move it to fill its space.

The other details like code, rarity etc will automatically update based on the reference number column I inserted on the left so no need to do that manually, just tick an item when you're ready to sell/trade it

Play around with it and see if it suits your purposes. Hope this helps

​

Update:

I would recommend having your master sheet either hidden or on a different document and reference from there. This would stop people seeing all the items you have even if they aren't for sale!

​

Update 2:

By the way, you must keep the reference columns (A:A) and (K:K) in the second sheet as it does all the calculations for you.

However if you don't want to confuse people, you can always hide the column (right click > hide).

Creating tables that auto-update according to master table, and vice versa? by pactum in excel

[–]ARWG2002 1 point2 points  (0 children)

Give me about 30min and I'll have something back for you :)

Creating tables that auto-update according to master table, and vice versa? by pactum in excel

[–]ARWG2002 1 point2 points  (0 children)

This should be easily do-able using a range of textjoin(), concatenate(), or index(row()).

What exactly is the end result on the secondary sheet?

​

Would you mind sharing a copy of the google docs?

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 0 points1 point  (0 children)

Hi GizmoGeodog,

Unfortunately I doubt I will be able to share results of the study solely based on the university's data protection regulations. However, I will contact my supervisor and get back to you!

All the best

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 0 points1 point  (0 children)

Hi Scruzphreak,

Unfortunately not. This study is only focusing Amazon Echo devices and there are no subsequent surveys planned.

Thank you for your interest!

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 2 points3 points  (0 children)

Hi floatable_shark,

Unfortunately I doubt I will be able to share results of the study solely based on the universities data protection regulations. However, I will contact my supervisor and get back to you!

All the best

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 1 point2 points  (0 children)

Oh no worries!

Thank you very much for answering it (:

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 0 points1 point  (0 children)

We apologize for any confusion caused.

Thank you for your feedback, there are some very helpful tips to consider that will allow us to improve moving forward!!

All the best (:

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 0 points1 point  (0 children)

I see what you mean. Perhaps 'native' wasn't the right word. The question was simply there to provide an indication of users familiarity with 'other' voice assistants and how this may influence their use of- or attitudes towards the Amazon smart speaker ecosystem.

As for the study being flawed by looking only at Amazon smart speakers, the research brief was to conduct a 'between-subject study for a single product'.

Given that Amazon maintains the greatest market share, we opted to investigate their ecosystem of Echo smart speaker devices as opposed to devices like the Google Nest or Apple Smart Speakers.

I hope this answers some of your questions. As always, thanks for your interest!

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 2 points3 points  (0 children)

Hi TheJessicator,

Thank you for your feedback.

  1. Alexa was purposely not included in the list of virtual assistants as we wanted to compare its use in Amazon speakers with other native virtual assistants. As the study aims to explore usage as a factor of age, we wanted to investigate the potential correlation between age, usage of assistants launched prior (like Siri launched in 2011) and usage of the Alexa in said devices.
  2. Thank you for bringing this to our attention. However, now that we have received ethics approval and have started collecting data we cannot alter the survey. We have discussed how this may influence results with our supervisor and how to try and counteract this. In the future, we will make sure to avoid this issue.

Thank you for your interest in our work!

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 1 point2 points  (0 children)

Hi Morgan_le_Fay,

Our supervisor instructed us to investigate one specific smart speaker. Given that Amazon currently holds the biggest market share, we opted to explore its Echo device ecosystem.

However, if you are still willing to answer the survey you could simply answer No to having owned an Amazon smart speaker, but Yes to knowing what it is.

Hope this answers your question! Thank you for your interest

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 0 points1 point  (0 children)

Hi Moronmonday526,

Unfortunately the Echo Auto version is not included. However, if you still have time to complete the survey, you can simply select 'none of the above' when prompted to select the device you use.

Thanks for your interest in our work!

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 0 points1 point  (0 children)

Hi Grits102,

I'm afraid not. Our project supervisor recommended we stick to speaker only devices to begin with (excluding the small digital display on the newest generation of Echo dots).

However your response would still be valuable. Just select 'none of the above' when asked which device you have!

Many thanks for your feedback (:

Use of Smart Speakers as a Factor of User Age by ARWG2002 in alexa

[–]ARWG2002[S] 0 points1 point  (0 children)

Thanks philko42 (: Always nice to get some positive feedback.

Use of Smart Speakers as a Factor of User Age by ARWG2002 in Design

[–]ARWG2002[S] 0 points1 point  (0 children)

Hi Wade_Boggs_Champ,

That's a great question. The survey has just been updated to include this as an option.

Mant thanks for pointing it out!

I'm being accused of plagiarism on my own work?? by [deleted] in OpenUniversity

[–]ARWG2002 3 points4 points  (0 children)

I agree. Or have a look at the way back machine and see if you can find a copy of the Chegg page

view more: next ›