INDIRECT(A1:A5&"!A1) to spill A1 from other sheets doesn't work, how could I do it ?

Administrative Note:

Welcome to the forum.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

Thank you. How exactly do you want the sheets to spill?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Hi,
use:

Formula:

Please Login or Register  to view this content.

That doesn't do it - I believe the OP wants everything from all sheets.

If it's column A only, this might work:

=VSTACK(Sheet1:Sheet4!A:A)

EDIT: No, that won't do it, either.

We need a sample workbook.

Reading the requirement... tanasedn's DOES do what was asked, IMHO.

Reading it again, it might, but a sample workbook would help to clarify the requirement, as would some response - any response! - from the OP.

Thanks, but you haven't shown us (mocked up) the results that you want. Is it just cell A1 from the sheets listed? Still not clear - sorry.

EDIT: I have just realised that there are more worksheet tabs - you should have saved it with them all visible! I srolled left - didn't realise I'd have to do this.

So, in S1:

=VSTACK(Sheet1:Sheet3!A1)

which is what Diana suggested in post #5.

You could add Start and Stop sheets to use as delimiters, then add sheets that need to be included only between them (see attached).

Then you could use this:

=LET(v,VSTACK(Start:Stop!A1),FILTER(v,v<>0))

See post #13.

Can you please mock up a realistic and NOT simplistic representation of what you want the results to look like?

Here is the document that is the closest to mine.
Thank you for your help.
Also, all the values errors are normal in the columns N O P of MAPPING, its because some location are not set in the pricing sheets and it is not a problem.
There are multiple tabs, I hope they wont be hidden this time.

This bit:

VALUE(IF(LEN($A$3)=5,LEFT($A$3,2),IF(LEN($A$3)=4,LEFT($A$3,1))))

returns an error. So, the formula in column N CAN NOT be working. Additionally, S (the intended result) does NOT EXIST anywhere in column A of any of the other sheets.

I think a defintion of what you are trying to do would help...

Totally baffled! Point me to some rows where the first INDEX MATCH returns nothing, but the latter part of the formula DOES return something.

First of all, thank you for your reply.

I made a mistake while writing the function and instead of $A$3 it should be $K3.

VALUE(IF(LEN($K3)=5,LEFT($K3,2),IF(LEN($K3)=4,LEFT($K3,1))))

This part looks if the postal code only has 4 digits, it just return 1 digit from the left, and if 5 it returns 2.
It allows me to have pricing depending on the postal code OR the area.

It didn't impact the result, just changed the error of the values that were not found.
I changed it and put the file back here.

Also to be more clear about what I want to achieve:

In the tab : MAPPING

There is the function in the Cell N3.

I need this function to spill to the bottom automatically. I don't want to drag it down. My databases change size a lot and I wish that it would just like the other columns.

My second wish is that the function in the cell O3 would spill down and right but that is maybe impossible i don't know.

What I put in the column R is what I thought would work in N3.


Also I feel like the title of my post doesn't fit the problem, I don't really know how to name it.
It really is the fact that I can't put a range of path to sheets in the function "indirect" to retrieve values from the said sheets ...