Formula effected by cell insert, and Vlookup repeats?

[blooper]

Hello!
Question 1: When inserting cells (pushing old data in those cells down to the next row), how do you keep formulas from following those pushed down cells or rows? I only want the data to change, not the areas where the formulas look for that data.

Question 2: When using a VLookup formula and in the table array there is more than one of the same lookup value, how do you make sure that all of the returned column values show up rather than the same one being repeated?

Question 3: Do the newer versions of Excel allow more than one column to be copied by way of text-to-columns?

Thank you!

[FDibbins]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[blooper]

Thanks. Sorry about that. I just changed the title to reflect what I was after.

[ChemistB]

Q1: If, for example, you have in B3
=VLOOKUP(A3,$K$18:$L$20,2,FALSE)
and you insert a row above it (moving the formula into B4). You will then have
=VLOOKUP(A4,$K$19:$L$21,2,FALSE)

If the Lookup Table was on a separate sheet or of you only inserted cells into Col A and B, then the lookup range would not change and it would be
=VLOOKUP(A4,$K$18:$L$20,2,FALSE)
What part are you not wanting to happen?

Q2: VLOOKUP will only return the first instance. Do you want multiple results returned to a single cell, or do you want each result in a separate cell? You'd need to supply an example of your layout and what you want the final result to look like in order to answer this one.

Q3: Not sure what you mean by more than 1 column copied? Do you want to select multiple columns and then have the results spread out based on what you entered as a destination cell? If so, the answer is no. Again, if you want a solution to a specific problem, post a spreadsheet and maybe there's a work around.

[blooper]

Q1: If, for example, you have in B3
=VLOOKUP(A3,$K$18:$L$20,2,FALSE)
and you insert a row above it (moving the formula into B4). You will then have
=VLOOKUP(A4,$K$19:$L$21,2,FALSE)

If the Lookup Table was on a separate sheet or of you only inserted cells into Col A and B, then the lookup range would not change and it would be
=VLOOKUP(A4,$K$18:$L$20,2,FALSE)
What part are you not wanting to happen?

Q2: VLOOKUP will only return the first instance. Do you want multiple results returned to a single cell, or do you want each result in a separate cell? You'd need to supply an example of your layout and what you want the final result to look like in order to answer this one.

Q3: Not sure what you mean by more than 1 column copied? Do you want to select multiple columns and then have the results spread out based on what you entered as a destination cell? If so, the answer is no. Again, if you want a solution to a specific problem, post a spreadsheet and maybe there's a work around.

I guess I should have separated this thread into 3, but I didn't want to overload the forum.

For Question 1, I'm curious about any formula, not only VLOOKUP. So here's an example of what I have that keeps changing when I insert data cells above the old data cells it relies upon:

=IF(SUM(INDEX(($B$2:$F$41=K2)*ROW($B$2:$F$41),,))=0,"",SMALL(INDEX(($B$2:$F$41=K2)*ROW($B$2:$F$41),,),SUMPRODUCT(--(($B$2:$F$41=K2)*ROW($B$2:$F$41)=0))+1)-2)

If I insert new cells from B2:F2 the equation changes to the following:

=IF(SUM(INDEX(($B$3:$F$42=K2)*ROW($B$3:$F$42),,))=0,"",SMALL(INDEX(($B$3:$F$42=K2)*ROW($B$3:$F$42),,),SUMPRODUCT(--(($B$3:$F$42=K2)*ROW($B$3:$F$42)=0))+1)-2)

You can see that the range goes from $B$2:$F$41 to $B$3:$F$42

What I need is for the range to remain $B$2:$F$41 no matter how many times I insert new cells at B2:F2

Question 2: I'm sorting data in descending order and would like each result to be in its own cell, but with no repeats. You'll see in the attached file that there are repeated #'s in column V. What I'd like is, for example, the number '11' to show up only once in row 4, followed directly below by '17' and '25' in rows 5 and row 6 respectively, since all three have the column U value of 128. Then in row 7 should continue with '27' which has the next lower column U value of 123.

Question 3: If you take a quick look at this youtube video at 1:57 he seems to be able to paste many columns at once into the appropriate spreadsheet columns by using text-to-columns. In my 97 Excel, I'm getting an error saying that only 1 column (1 column spread to more than one column) can be done at a time. I was wondering which newer versions allow for what he's doing in the video. I'd like to be able to enter data this way, but I don't know if it's worth it to upgrade for this alone.
http://youtu.be/R1acmWtFovQ?t=1m57s

Thank you for bearing with me!

[blooper]

Should I split this question up into 3 separate threads?

[FDibbins]

for Q1. if you want that part of the formula to remain unchanged when you insert rows above it, try using indirect()

=IF(SUM(INDEX((indirect($B$2:$F$41).......................

Q2. Heres what I did to handle the tie-breaks.

I added a helper column after S and copied this down. You can hide this column if you want...
=IF(COUNTIF($S$1:S1,S1)>1,S1+(COUNTIF($S$1:S1,S1)*0.1),S1)
then in W, I changed your formula to...
=VLOOKUP(LARGE($T$1:$T$40,ROW(A1)),$T$1:$U$40,2,FALSE)

Q3. Im using 2007, and that can only do 1 column at a time

[FDibbins]

No, I think you are fine here, and on a side note, it is not necessary to use "reply with quote" unless there is a specific part of some-one's post you wish to highlight

[ChemistB]

In the You-tube video, it's only 1 column, it just looks like more because it's spilling over. If he'd auto fitted col A, it would have all been seen to be in col A.

To pull back more than 1 result, first we need to give each number in S, a unique identifier. I did that in Col U, like so
=S1&"_"&COUNTIF($S$1:S1,S1) copied down.

Then to the right of your values that you sorted, now in W, (you'll need to research INDEX/MATCH a bit), I used this formula dragged to the right and down

=IF(COUNTIF($S$1:$S$40,$W1)<COLUMN(A1),"", INDEX($T$1:$T$40, MATCH($W1&"_"&COLUMN(A1), $U$1:$U$40,0)))
See attachment
Questions?

[blooper]

Thank you both for very interesting solutions to the VLookup repeats. Since I want the results to show up in a single column rather than across many, I will be using the FDibbins solution. But it just goes to show me that there are more ways than one to skin a cat. I studied both for quite a while trying to learn what you did. I suppose subsequent releases of Excel take into account these work-arounds and try to come up with new functions that do the same things in fewer steps?

Anyway, although the other 2 questions are answered, I still have Question 1 bugging me. The INDIRECT function keeps giving me errors, and I can't figure out why. The attached sheet shows a simple FREQUENCY formula in column L that works, but I copied that formula to column N and tried to use INDIRECT, and I get #REF! errors and N/A's. What am I doing wrong here?

Thanks!

[FDibbins]

what are you trying to do with the indirect()?

[blooper]

I'm trying to insert new data at the top of a range that formulas are looking to do their calculations. Whenever I insert new cells at the top of that range (or last night even cutting and pasting without cell insert) the formulas change to follow the old data. You'll see in the attached sheet that after inserting cells above the old data, the formula in column L (and N)change from B2:F9 to B3:F10. I want the formulas to refer permanently to the original row/column range. It surprises me that entering new data in a permanently fixed range isn't common enough to have a simple method of doing so. It's frustrating having to change back formulas every day I enter new numbers. Help me!