-5

The following question, after receiving -2 votes, pushed over the question-ban threshold:

What is the best way to resize one Excel table based on another?

I have a workbook with a number of sheets that, at the top left, have a table loaded via Power Query. They have other tables, further to the right and non-adjacent, that cannot be computed via Power Query but that perform calculations on the first table, and ideally, to reduce calculation overhead, should have the same size as the first table. (Currently, they're drug down way below the table they refer to, which works, but is dreadfully inefficient.)

To give an example, say the Query table is at A1:M100. The calculation table would be at Z1:AD100. Let's say the query gets refreshed, and ten rows get added. I then want my other table to occupy Z1:AD110.

How can I best accomplish this?

Are there drawbacks into merging the query/calculations into one table, specifically if other columns were added to the query? Is there a setting where the number of rows in my calculations table can be dynamically adjusted (this would be ideal)?

If not, I know I can use VBA to trigger the update, perhaps tied to a button that updates the query, but I'm looking for a non-VBA solution, if practicable.

The calculations are such that I don't think spill arrays (which I know how to do) are feasible.

Thanks!

I'm looking for suggestions from more experienced users here on how to improve this question (which I have since deleted) so that it will be well-received and I can re-post it. I would greatly appreciate assistance that would apply to questions in general, and to this one in particular. Just to be clear, I'm not looking for an answer to the question here. I want to know how I can improve it and, eventually, undelete it and have it received well.

The one comment, from a user with over 10x my rep, said "I downvoted because there is no logic in the question at all." While I respectfully disagree, I also appreciate that the comment's author genuinely believed my post seemed illogical and not well thought out. And I also understand that sometimes things make sense in my head but, when I try to spell them out, the sense gets lost.

It seems on topic, it's not opinion based, though I suppose the title could make it seem opinion-based. It asks a specific question (actually a couple questions, maybe I should address that).

I'm not looking for an answer to my question and and I'm not asking to be un-banned. I'm not looking for anyone to rewrite my question for free either. I just want suggestions on how my question and others I might post like it could be improved to the point that they would be well-received.

I edited and reposted the question at https://stackoverflow.com/questions/79713917/excel-vba-trigger-macro-when-power-query-table-is-refreshed.

12
  • 15
    I don't know much about Excel or Power Query, but as of current writing, since you're not looking for a VBA solution, it doesn't look like related to programming, thus off-topic on SO. Commented Jul 25 at 2:04
  • 20
    For the record, you have 7 other deleted questions with score of 0 or less which are contributing to the question ban. This most recent question was just the thing that pushed you over the edge and not the sole reason for no longer being able to ask questions. Commented Jul 25 at 2:29
  • 8
    "but I'm looking for a non-VBA solution, if practicable." But what does that mean? To do it programmatically using some programming language? If so... which one then? What have you tried? If to do it in Excel without any programming... then why do you post this question on a site which is specifically for programming problems? Commented Jul 25 at 7:59
  • 6
    Reading the question, I agree, it doesn't look like something that can be made on-topic on Stack Overflow as it doesn't appear to be about programming or software development. The question itself might be op-topic on Super User, but we can't improve the question to be on-topic on Stack Overflow as the subject is the problem. Commented Jul 25 at 8:03
  • 1
    Ask 1 question. Commented Jul 25 at 19:27
  • @HenryEcker would this question be migrated to another stack exchange site? I'm not sure which one would be applicable but I feel like there's gotta be a site somewhere Commented Jul 25 at 22:29
  • 2
    If it there were there's no point in migrating it. @BlueRobin it doesn't help get this user out of the question ban and it would just start with an old time stamp and a negative score on the new site. It'd be better to just post a new question. Commented Jul 26 at 0:26
  • you should try to fix all your questions (including the 7 deleted ones) otherwise you're putting all your eggs in one basket. It will likely take 8 times longer to fix things if you only fix one question. Commented Jul 31 at 19:30
  • When is "EDIT"/"UPDATE" appropriate in a post? Why should I not upload images of code/data/errors? Commented Jul 31 at 21:23
  • 1
    Do not delete & repost questions, edit them. Commented Jul 31 at 21:33
  • It's good you say "and others I might post like it", and better, others I posted like it, because it's not just 1 question that causes a rate limit, it is multiple. Commented Jul 31 at 21:34
  • Re the linked post: Ask 1 question. Don't partially phrase the requirements in multiple parts of which none are actually the full question. Include only but all that is needed to ask. If you include working code, clearly explain how it is restricting or demonstrating what the question is asking for. Don't include dead ends. Either ask about 1 bad function, with a [mre], or about how to reach a goal, given working parts only, ideally with a [mre]. PS Yes or no questions are seldom useful & seldom asking for what is actually wanted. How to Ask Help center Commented Jul 31 at 21:55

1 Answer 1

Reset to default
14

First, the question looks primarily opinion-based, because it asks for "the best way", without defining a criterion to say that an option is better than others and the lack of focus. --ref Are questions asking for "The best way" always primarily opinion-based?

The question looks too chatty, the example provided doesn't include enough details and doesn't show any effort to find the expected behavior.

On the other side, it is unclear if by Excel table the question is referring to the feature named table or if it's referring to a variable worksheet region.

3
  • It seems like OP wants some values to appear in the Z101:AD110 rows, but without any indication of what those values should be or where they should come from. I further guess that "ten rows gets added" means that A101:M110 get populated...? But what is the rule that tells us that there were 100 rows before? What is the rule that tells us that there are now 110 rows? What if there are gaps, for example? Commented Jul 25 at 18:55
  • The range references in the question are A1:M100, Z1:AD100, Z1:AD110 :) Commented Jul 25 at 19:03
  • Power Query results shown in Excel commonly use the Excel feature called Table. If the OP uses structured references in Excel tables, then the "problem" might be trivial. It depends on how the calculations are done. As the OP, is using A1 notation references (A1:M100, Z1:AD100, Z1:AD110) instead of structured references, it's unlikely that they are using structured references. Commented Jul 25 at 19:17

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.