Concatenate function...same spread sheet different tabs

[saffrondbull]

Hi,

I want to concatenate two columns in a separate tab but when I do, the values appear as =Sheet1!I$8,Sheet1!J$8,...instead of Sheet1!I$8,Sheet1!I$9,etc. Pls help.

Thanks

 

[nbrcrunch]

you have to manually type it.

either:
=Sheet1!I$8&Sheet1!I$9


or
=CONCATENATE(Sheet1!I$8,Sheet1!I$9)

BTW, something simple like this, you could have saved yourself from having to wait for an answer by merely checking Excel's built in help system.
.

 

[saffrondbull]

Thank you for the welcome and sorry for the Martian language. I tried to make my life easier by concatenating the values in the same tab and so far, so good. However, when I tried linking the results to a different tab, that's where my woes started again.

The cells should show Sheet1!J8...Sheet1!J9...Sheet1!J10...
Instead what I'm getting is Sheet1!J8...Sheet1!K8...Sheet1!L8...
I know this may sound elementary guys, but I'm just so pressed for time. I really appreciate all the effort to help coming from your end.

Thanks!

 

[jasonb75]

What is the formula you're using that is giving the wrong result?

 

[saffrondbull]

I'm just typing the equal sign in the destination cell, go back to the tab with the desired values, point click then hit enter. Then I would drag the cross at the corner of the cell across the row up to the column where I need the values to appear.

Again, what i need to appear in Sheet 2, D2:AG2 are the values in Sheet 1, I8:I37

Thanks in advance!

 

[jasonb75]

Looks like you need TRANSPOSE instead

Copy this formula
=TRANSPOSE(SHEET1!$I$8:$I$37)

Then in sheet 2, click and hold on D2, then drag the mouse to AG2 to select the full range.

On your keyboard:-

Press F2

Ctrl + v

Shift +Ctrl + Enter

Based on the information in your last reply that is what you need.

 

[saffrondbull]

I've been meaning to automate sheet 2 and the rest of the sheets by linking the values from the other tabs...it's a grade book actually, meant to lessen paperwork and save time.

I know the people in this forum has the answers, it's just me who doesn't know how to ask/phrase the right questions. I'm grateful still the same.

 

[jasonb75]

Easy to see, hard to explain.

Did it do what you need though?

 

[saffrondbull]

yep, and it's ok for a single use; but I've been meaning to share the file to other teachers who are unbelievably more innocent w/ excel than I am.

Something to the effect of, all you have to do is type in your students' names in one master file, and whaam...the rest of the sheets will auto populate with these basic information.

 

[nbrcrunch]

Perhaps if you posted what the desired end-result would be, we can offer a simpler design recommendation. In other words, give us the big picture... Something like:

"I have a classroom of 25 students that i am tracking grades for throughout the school's "year" (typically running from September of one year until May/June of the following year.) Each student will have a yet undetermined number of graded assignments, tests and pop-quizzes. I need to track and summarize each of those, grouping by student, semester and type of graded study (homework, test, etc.)."