Converting data to new spreadsheet with different merged cells

[Delaney21]

I'm struggling to come up with a formula to do this. Can anyone help?

OLD SHEET:

B9: Contact 1
B10: Contact 2
B11: Contact 3


NEW SHEET:

C5:9 are merged and I need B9 to populate from the old sheet
C10:14 are merged and I need B10 to populate from the old sheet
C15:19 are merged and I need B11 to populate from the old sheer

 

[magpie2000k]

=[OLD SHEET]Sheet1!$B$9

Just from old sheet =b9

Or am I missing what you are asking

 

[Delaney21]

=[OLD SHEET]Sheet1!$B$9

Just from old sheet =b9

Or am I missing what you are asking

I'm not that basic Sorry, I probably wasn't clear. I have about 100 lines, so I would like to take the formula and copy it down so it would work, but when I do =B9(old sheet) and copy down, since the new sheet has merged cells, it doesn't go down to B10, it goes to B14. I need a formula that I can use to copy all the way down the sheet, so I'm not doing it all manually. Is it possible?

 

[Delaney21]

I need help getting data from 1 tab of a workbook to another tab, but I can't do a drag down a formula because the 2nd tab has merged cells. So on the merged sheet, if I drag down =Sheet1!A1 to the next cell, it pulls it as =Sheet1!A5, when I need it to be =Sheet1!A2. I asked this question before and I don't think it was clear so I didn't get a correct answer, so I'm trying again with sample tables below to show how I'm doing it. I added in 2 tables for tab 2 since I wasn't sure how to show merged cells in the tables on here.

I hope someone can help because I have a FULL sheet that I need to do this with, which would take forever to manually enter the formulas!!!

Tab 1:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sara[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jessica[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Frank[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Tab 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merged with A1:A4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]​Merged with A1:A4 Formula is =Sheet1!A1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]​Merged with A1:A4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]​Merged with A1:A4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Merged with A5:A8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Merged with A5:A8 Formula should be =Sheet1!A2, but drags down as A5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Merged with A5:A8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Merged with A5:A8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Tab 2 - alternate view:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Merged 1:4[/TD]
[TD]=Sheet1!A1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Merged 5:8[/TD]
[TD]Drags down as =Sheet1!A5 instead of A2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Merged 9:12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 400.488px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]Merged with A5:A8
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Delaney21]

Here is a link to the pictures of the file, if that helps!!
Tab 1:
https://photos.app.goo.gl/3xhtctjrI6sszjMG3

Tab 2:
https://photos.app.goo.gl/NBkwT2h2kczf6Uvp1

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. As per forum rules (rule 12 here: Forum Rules).
I have merged the two threads.

 

[Delaney21]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. As per forum rules (rule 12 here: Forum Rules).
I have merged the two threads.

I tried that and no one else opened it.

 

[Fluff]

I tried that and no one else opened it.

That does not mean you can start a new thread.

 

[Delaney21]

That does not mean you can start a new thread.

noted, thanks for your help.