Copy fill without stepping value

[aod]

I have excel 2007. I have a workbook with 2 tabs set up as follows:

• Tab A
• Tab B

Tab A contains a column with the source data, which will be constantly overwritten

Tab B has a row which references the data in tab A

I would like to copy the reference formula in Tab B down for say 100 rows, but when I use the fill tool, it automatically moves the reference cell up one. I want it to remain the same as the original.

E.g
Tab B, Row 1

ColA = Tab A, Row 1
ColB = Tab A, Row 2
ColC = Tab A, Row 3

When I fill copy the above; on Tab B, Row 2, the formula changes to ColA = Tab A, Row 2, ColB = Tab A, Row 3 etc. I would like them to remain as ColA = Row 1, ColB = Row 2 etc.

I will then use Tab A to input each rows data in Tab B, having converted the previous row into just text so it doesn't change with each update.
Hope this makes sense.

[dogberry]

Hi

If you want a reference to be static you need to use the dollar sign in your cell reference ie $A1 the A will be constant the one will change or use $A$1 both will remain the same


Chris

[Ace_XL]

In Tab B Column A use

Please Login or Register  to view this content.

drag across

[aod]

@dogberry,
Thanks for your help. Unfortunately when I did this, the cell changed from the reference data, to show the formula text instead.

Moderators Note: As per Forum Rule #12, don't quote whole posts unless necessary-- it's just clutter...Thanks.

[aod]

@Ace_XL,
Thanks, I'll try and work this out and give it a go.

Moderators Note: As per Forum Rule #12, don't quote whole posts unless necessary-- it's just clutter...Thanks.

[aod]

OK the above worked, although only on the same tab that it came from.

I think I'll put this in context try and work out a better way of doing this.

I'm helping my dad create an address book, so he can do mail merge address labels. For various reasons, I am recommending he uses Google Contacts for his address book.

My idea is as follows:
Using an online postcode / address finder (to save typing), copy the address for each person, into an excel spreadsheet. As default these will be in column format.
I was hoping to create a tab where Dad can paste the address, and maybe a macro button to 'submit' the address, which adds it to a row on the next tab and clear the input tab for the next entry. This 'submission tab' would have field headings that match Google Contacts, so hopefully I can import this as a csv into Google.

I can then use the data to create a mail merge in Google Docs.

I'm pretty sure this is fairly complex, but i am a bit of a nerd, so if anyone can assist please let me know! If there's a quicker, easier way, I'd be interested in that too!

[aod]

I've just tried using a data entry form, and this does pretty much exactly what i want, however, it relies on the user manually typing in each field. It would be better if I could get the data copied / pasted from the online address finder tool, into excel, transposed to row format, for each record.