Google Answers Logo
View Question
Q: Excel Spreadsheet question ( Answered 4 out of 5 stars,   3 Comments )
Subject: Excel Spreadsheet question
Category: Computers > Software
Asked by: markc-ga
List Price: $5.00
Posted: 13 Jun 2002 06:52 PDT
Expires: 20 Jun 2002 06:52 PDT
Question ID: 25195
I have uploaded the spreadsheet as an html file it is 2.25 meg so it takes a
second to load....
The question I have is: In the first field is the product id (prodID)
I need to see if there is a way to have this number appear
automatically through some sort of Macro or something in the 2 fields
prodImageSmallPath and prodImageLargePath.
For instance
say prodID is 884 I would need prodImageSmallPath to be
images/tn_884.jpg and prodImageLargePath to be images/884.jpg
Is there anyway to make this happen?
Just some additional info this Excel file is exported from an Access
data file the only reason I mention this is if it cannot be done in
Excel could it be done in Access? I would prefer Excel but at this
point willing to try anything.
Subject: Re: Excel Spreadsheet question
Answered By: lazerfx-ga on 13 Jun 2002 07:22 PDT
Rated:4 out of 5 stars
Hello Markc

You will need to use the 'CONCATENATE' function to do this properly.

Depending on the version of Excel you are using, the actual
implementation may differ.  I am using Excel 2002, however I will try
and point you to the right way to do it in 2000 as well.

The function itself looks something like this:

Excel 98/2000:
=CONCATENATE("images/tn_", $A$2, ".jpg")
Excel 2002:
=CONCATENATE("images/tn_", A2, ".jpg")

(Change A2 or $A$2 to have the same value as the cell you wish to

So, what does this mean?

This starts the function off by telling Excel there is a function to
be applied, that it is the concatenate function, and opens the
brackets.  Everything within these brackets is part of the function.

This is a string, held within quotes.  This is the first thing to
concatenate (Or add together)

, or &
The comma is used to split different things apart in functions.  In
this case, it is used to split the various input options apart.  The
Ampersand symbol (&) is used in the non-function version to say,
"output this AND this AND this".

In between difference versions of Excell, Microsoft has changed the
meaning of the $ sign.  In older versions (Up to 2000) it meant to set
this as a 'relative' cell reference.  When you copied the cell, it
would automatically update, so if you copied it onto row 4, it would
automatically point to $A$4.  However, in version 2002 it means
exactly the opposite.

This closes the function up, and says to excel that you have finished
all you wish to do.

To recap:

Excel 98/2000:
=CONCATENATE("images/tn_", $A$2, ".jpg")
Excel 2002:
=CONCATENATE("images/tn_", A2, ".jpg")

This information was taken from the Excel 2002 help file.

markc-ga rated this answer:4 out of 5 stars

Subject: Re: Excel Spreadsheet question
From: hogwash-ga on 13 Jun 2002 07:24 PDT
Or you could similarly use the '&' operator as shown here:

Subject: Re: Excel Spreadsheet question
From: anupamrupa-ga on 13 Jun 2002 07:33 PDT
Instead of A2 or $A$2, just put it as $A2. That would ensure that it's
always column A that gets catenated and the row number will be
relative. A2 will also work. However, $A$2 will give wrong results.
The function for the LargePath column, as should be obvious from the
explanation by LazerFX, will be

To put it in lay-man terms, you need to go the first cell that
currently has images/tn_30474.jpg. Edit this cell (press F2) and
replace it with =CONCATENATE("images/tn_", $A2, ".jpg"). Press Enter.
Now copy this cell by using Ctr-C and paste it across the entire row
of data using Ctr-V. (An easy way to select the entire column of data
is to go to the first data cell in the column and then press
Shift-DownKey-End Key) Press enter, and the the entire column will
contain the desired values. (Other cells will automatically chage
formula to $A3, $A4, and so on) Repeat the process for the next
column. Just be sure that the formula this time is
Subject: Re: Excel Spreadsheet question
From: lazerfx-ga on 15 Jun 2002 07:40 PDT

$a$2 will work, as all you are going to be doing is copying
vertically.  If you were copying horizontally then you would need to
do A$2.

The relative positions will be the same.

An example:

| src  | stuff| stuff| stuff| func |

If you copy horizontally or vertically, and have $A$1 in the 'func'
field, the src will always be 4 cells to the left of the func field.

If you have $A1 in the 'func' field, then the src will always be in
the first colum, but could be on any row.

If you have A$1 in the 'func' field, then the src could be on any
colum, but will always be in the first row.


Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy