referring to cells without changing contents

June 23, 2010 at 07:59:12
Specs: Windows XP
Hi all,
This might be a bit of a silly question, and I'm most likely completing overlooking something, however - I am creating a mail merge document that reads from excel. that part works fine.

What I want to do is, for example,

I want to have a list of servivce providers in a dropdown validation list. But, when I select the names on the dropdown, which would refer to a names range in another sheet, I dont want the cell in which I selected the dropdown cell to change, i.e. If i select Council, which would refer, say to A1:A3, I dont want it to give me the contents of those cells when I clock Council, I just want it to remain the same, saying Council, but when I do the mail merge, it will come out at the cell values mentioned.

Does that make sense?
Thanks


See More: referring to cells without changing contents

Report •

#1
June 23, 2010 at 08:30:06
I'm a tad confused.

re: If i select Council, which would refer, say to A1:A3, I dont want it to give me the contents of those cells when I clock Council, I just want it to remain the same, saying Council,

How would a drop down give you the contents of of A1:A3?

A drop down will always just show the selection that was chosen. What you do with that selection is up to you, but you can't get the drop down to show the contents of a range of cells.

Am I missing something in your explanation?


Report •

#2
June 23, 2010 at 08:47:27
Hi Derby, apologies, it isnt very clear indeed.

You know when we use a dropdown to refer to a named range, i.e. =Council (which would refer to those A cells containing the mailing address of the Council), and we select it, it gives us the contents of the named cells, so it would come back with the mailing adress?

I dont want it to do that,I still want it to refer to the mailing address in the mail merge, but cosmetically in the workbook i just want the select to say 'Council.

Hopefully that is more clear! thanks so much for the help


Report •

#3
June 23, 2010 at 12:18:56
I don't do too much with Mail Merge, so I'll offer this suggestion from a Excel perspective only.

If I made a list of all of the names of my Named Ranges, I could create a Drop Down that displays those names.

If I wanted to use the text that is selected in the Drop Down as the Range Name in a formula, I could use the INDIRECT function.

For example...

Let's say Council refered to A1:A3 and Flibosh refered to B1:B3.

A1:A3 contains 100, 200 and 300
B1:B3 contains 150, 250, and 350

Put Council and Flibosh in C1:C2

In D1, create a Drop Down list that refers to =$C$1:$C$2, making those 2 words the choices in my Drop Down.

All of these formula will return the same results when you choose Council in the drop down. When you choose Flibosh, the last formula will return the SUM of B1:B3

=SUM(A1:A3)
=SUM(Council)
=SUM(INDIRECT("Council"))

=SUM(INDIRECT(D1))

INDIRECT(D1) will use the text string found in D1, recognize it as the name of a range and sum the range.

Now, how you would use this as part of your Mail Merge I'm not sure. You are probably more familiar will Mail Merges than I am, so I'd be interested if this helps or not.

Can you direct a Mail Merge to look at the text in a cell and use that as the name of the range that contains the data for the merge? If so, then the INDIRECT function might help.


Report •

Related Solutions

#4
June 23, 2010 at 14:02:27
Hi,

Maybe I have got the wrong idea about what you want to do, but it seems to me that mail merge is not necessary for what you want.

Lets say that you have a 'Form' letter that could be sent to a number of different organizations, possibly including some information specific to that organization, and of course has that organization's address and perhaps contact person's name.

As your Excel spreadsheet is selecting one organization at a time, you only need a simple set of links from the Spreadsheet to a single, standard Word Document.

In the spreadsheet have a table of organization names, addresses and other organization specific information.
The first column has the organizations 'short' name, e.g. "Council"

In the cell used for the selection say B1, use the first column of the table (organizations short names) as the source of the validation list.

B1 will now select one of the organization short names

In cells in the row above your table use the standard VLOOKUP() to return the selected organization's data.

Say the table is in cells D2 to Z20
Column D is the organization short name
Columns E to I are address fields and further columns contain other organization specific data.

In row 1 column E put this:
=VLOOKUP($B$1,$D$4:$Z$20,2,FALSE),
Drag the formula along row 1 above the address fields, changing the offset number to match.

Now Cells E1 to Z1 will contain all the information required in your form letter, depending on the short name selected in the B1 drop-down.

Open the Word document containing the Form letter.
At the point where an address field is required, go to the relevant cell on row 1 and copy it. Then in the Word document at the point you want it to appear use Paste Special..., selecting 'Paste Link' and 'Unformatted text'. The text in the cell is now linked-to and appears in the Word document.

Repeat for all required fields.

Now you use the drop-down in B1 and all the fields in the letter will change.

You don't need named ranges in Excel, and you don't need to use mail merge.

Mail merge would be used if you wanted to automatically prepare letters to all the organizations in your list in one go. In which case no drop-down is needed as all fields in the selected table are used sequentially in the series of letters.

Regards


Report •

#5
June 25, 2010 at 04:38:02
Hi Humar,
I get where you're coming from. So realistically your idea is to make a list of all the organisations and their addresses horizontally in an excel sheet, and use a validation list of the short names to select the relevant contact.

However, I've replicated your idea a few times but keep getting the #N/A message on the cells where the result should be, bgut the VLOOKUP is referring to the correct cells


Report •

#6
June 25, 2010 at 04:57:23
re:
In row 1 column E put this:
=VLOOKUP($B$1,$D$4:$Z$20,2,FALSE),
Drag the formula along row 1 above the address fields, changing the offset number to match.

Instead of changing the offset number to match, try:

=VLOOKUP($B$1,$D$4:$Z$20,COLUMN()-3(,FALSE)

Since the formula is in Column 5 (E) and 5 - 3 = 2, the formula will pull data from column 2 of the table_array. As you drag it across Row 1, the col_num argument will increment.

This assumes of course, that your address data is in the correct order e.g. Name, Street Address, City, St, Zip

However, one issue I see with a VLOOKUP solution is that some addresses have one address line, while others have 2 (commonly know as Address 2):

Bob Heath
43 This St
Thistown, Thisstate
55555

Sue Treyfor
56 That St
The Highlands
ThatTown, Thatstate
33333

A VLOOKUP solution may result in blank lines for "Address 2" in your document, depending on how your database and/or document is laid out.


Report •

#7
June 25, 2010 at 06:03:59
Hi,

I used one row per address
and a 'short' name in column A to use as the lookup
As the short names in column A are used as the validation list, you should never get the #NA message

Can you post your VLOOKUP() formula and the address of the lookup cell and the range containing the addresses.

Regards


Report •

#8
June 25, 2010 at 06:12:32
Hi Humar,
No, still getting nothing here, perhaps I am doing something wrong?

Report •

#9
June 25, 2010 at 06:21:05
Hi,

Can you post your VLOOKUP() formula and the address of the lookup cell and the range containing the addresses.


Report •

#10
June 25, 2010 at 06:31:59
My code is =VLOOKUP($D$18,$B$2:$F$16,2,FALSE)

D18 is the dropdown from the validation list in column a
B2-F16 are the rows of addresses


Report •

#11
June 25, 2010 at 06:37:11
Hi,

Your range $B$2:$F$16 does not include the names in column A
VLOOKUP() is searching for the names in column B
VLOOKUP($D$18,$B$2:$F$16,2,FALSE)

Try this:
VLOOKUP($D$18,$A$2:$F$16,2,FALSE)

Regards


Report •

Ask Question