Alternative to Concatenate

[DaveKid]

Hi, I have a spreadsheet giving sales figures such as Year to Date revenue,
total number of sales, targets, pipelines etc. To provide me with the data I
want I seem to be concatenating alot of the data i.e. name, week number or
concatenate: name, week number, type of sale and so on and so on and I end
up with a huge sheet full of concatenated data from maybe 4 columns of
original data. I then write the formula in the front sheet to gather the data
from the concatenated columns to give the figures

Is there any way I can avoid this duplication of data? Can i not just ask
excel to look in these 3 cells and return the result instead of having to
concatenate another column?

 

[akphidelt]

As far as I've seen and done that is the best way to do it in excel. If you
ever have a chance, access would make that process incredibly simple. Another
way is to use pivot tables, which then you can slice it and dice it any way
you want.

 

[DaveKid]

Thanks for your help, Unfortunately I do not have access to access. Pivot
tables seem excessive for what I am doing. I would have thought you would be
able to simply look in the cells instead of having to concatenate each
different scenario. Hmmmm!???

 

[Ron Rosenfeld]

Is there any way I can avoid this duplication of data? Can i not just ask
excel to look in these 3 cells and return the result instead of having to
concatenate another column?

Yes, you can. But you don't provide enough data to say more than that.
--ron

 

[DaveKid]

Hi Ron, What data would you like me to provide you with?

 

[Ron Rosenfeld]

Hi Ron, What data would you like me to provide you with?

Not knowing what you are trying to accomplish makes advice very difficult.

You certainly haven't mentioned why you have to concatenate data.

You mention you are trying to get data on your "front sheet" but that your data
is on some other sheet.

The same techniques that allow you to put data on FrontSheet that is posted on
DataSheet should allow you to look at particular cells on DataSheet.

For example, to show on FrontSheet the SUM of column B on DataSheet, you could
use a formula of the type =sum(datasheet!B:B)

The same is true for other functions.

Pivot Tables can also be useful in presenting data in an organized fashion.
--ron