Use of Individual Cells from Different Sheets in an Array instead of Range of Cells?

[finalmike]

Is it possible to make it so an array has a list of cells to include vs. a range of cells (i.e. A2:A10).


So instead of writing A2:A5,B2:B5 you would have B2:B5 be something like ('Sheet 1'!B2,'Sheet 2'!B2,'Sheet 3'!B2,'Sheet 4'!B2,'Sheet 5'!B2)?

[Glenn Kennedy]

What do you want to do with the array?

[finalmike]

Similar to before. I want it to conditionally average data from different samples (1 per sheet) depending on if a "Yes" in selected in a drop down list.

[finalmike]

I tried AVERAGE(IF(A2:A6="Yes",('Sheet 1'!B2,'Sheet 2'!B2,'Sheet 3'!B2,'Sheet 4'!B2,'Sheet 5'!B2)) and enter it as an array but no luck. Thanks for helping.

[Glenn Kennedy]

Is that not overcomplicating it a bit?

See sheet. BtW.. also shows that STDEV.S is the modern equivalent of STDEV

[Glenn Kennedy]

At least when you use STDEV.S, there is no doubt that it is the SD of a sample of the population you are looking at... If for no other reason than that - STDEV.S is preferable to STDEV.

[finalmike]

Yeah I agree

[Glenn Kennedy]

Incidentally, just in case you had a "thing" whereby you didn't want to bring the actual values across into your master sheet, did have a go and while I could get the average to work, trying to get the STDEV.S to work was nigh-on impossible.

Anyway... you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[finalmike]

I would but the original question remains unanswered. I was hoping to avoid having to post all my data twice and organize it the way it needs to be. Plus I needed it for standard deviation too

[Glenn Kennedy]

Now I am a little confused. Can you post a sheet showing your desired layout for the raw data and the desired output. I can't guarantee that I can make it work... but I feel as though I am fumbling around a bit...

[Glenn Kennedy]

I think I may have found a way. It's a bit foootery... so (if you don't mind... and even if you DO mind ) I'll wait until you supply the desired input/output as requested above.

[Glenn Kennedy]

I may have to leave in about 10 minutes (not sure...) so, rather than wait, I have had a go...

see sheet. Include/exclude sheets from DD in column B and your answers appear as if by magic. I hope...

[finalmike]

Very nice. It seems to work great, but I am reluctant to use it until I understand how it works. I am kind of in a rush right now so I don't have time to sit down and figure it out. How does the formula know to refer to each sheet (ie what does "Sheets" do?) I thought INDIRECT was to use a formula to determine a cell to reference.

[Glenn Kennedy]

Sheets is a named range (CTRL-F3 to view) and refer to a list of the sheets to be examined. They are in A3 to A7. Ultimately it returns an array of the values in B2 of each sheet and multiplies it by whether or not the sheet has been selected for inclusion or not. In some of these array formulae, the evaluate formula tool is less than ideal, but... Formulas/Formula auditing/Evaluate formula will give you a good basic overview...

Anyhow, you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[finalmike]

Ahh, thank you! You certainly are a guru

[Glenn Kennedy]

You sent me a PM, asking how to make the formula "draggable". Try this... basically take the 2 outside the " and replace it with a counter that starts at 2 and increments by 1 per row.

[finalmike]

Thanks, this also works great. I appreciate all your help. Could you possibly explain how this works? I understand everything but the use/need for the N function. It's working on the Sheets array you made, but I don't really understand what it's doing. If I drag the N() portion of the formula you made down in the worksheet you attached, it makes a list of the values of Sheet1's B2 value,Sheet2's B3 value, Sheet 3's B4 value and so on.

In case someone asks me how it works, I'd like to be able to explain it myself, haha.

[Glenn Kennedy]

This: &ROWS($B$1:B2) is the counter. This resolves to 2 and in the next cell down, to 3, etc.

N has no role in the "draggability". In essence it keeps it all in an array. Without it, the formula evaluates only the first sheet.

[finalmike]

Ok cool. That works for me. Thanks