Cell Reference from another worksheet

[Ankitsahgal]

Hi All,

I have in sheet1 , the following data

[TABLE="class: grid, width: 433"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Employee Number
[/TD]
[TD]Name
[/TD]
[TD]Basic Salary[/TD]
[TD]Overtime[/TD]
[TD]Lieu[/TD]
[TD]Bonus[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Smith, John[/TD]
[TD="align: right"]£200[/TD]
[TD="align: right"]£10[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD]Porter, Helen[/TD]
[TD="align: right"]£210[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£35[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]Rayne, Alex[/TD]
[TD="align: right"]£300[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£50
[/TD]
[/TR]
</tbody>[/TABLE]


I want to summarize the above information in Sheet2 in the following format:

[TABLE="class: grid, width: 356, align: left"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]GL Code[/TD]
[TD]Description[/TD]
[TD]Amount [/TD]
[/TR]
[TR]
[TD="align: right"]2000
[/TD]
[TD]Smith, John Basic Salary[/TD]
[TD="align: right"]£200[/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[TD]Smith, John Overtime[/TD]
[TD="align: right"]£10[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD]Smith, John Lieu[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD="align: right"]3500[/TD]
[TD]Smith, John Bonus[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD]Porter, Helen Basic Salary[/TD]
[TD="align: right"]£210[/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[TD]Porter, Helen Overtime[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD]Porter, Helen Lieu[/TD]
[TD="align: right"]£35[/TD]
[/TR]
[TR]
[TD="align: right"]3500[/TD]
[TD]Porter, Helen Bonus[/TD]
[TD="align: right"]£0[/TD]
[/TR]
</tbody>[/TABLE]


















The actual data has a lot more information (Overtime1, overtime2,etc) and also a lot more employees.

I have tried to use Cell and Indirect function for reference but I am unable to get the desired result.

Can someone please help

Thank you

Ankit

 

[tygrrboi]

In Sheet2!$C$2 (the Amount Column)


=VLOOKUP(LEFT(SUBSTITUTE(B2," ","-",2),FIND("-",SUBSTITUTE(B2," ","-",2),1)-1),Sheet1!$B$2:$F$4,MATCH(RIGHT(B2,LEN(B2)-LEN(LEFT(SUBSTITUTE(B2," ","-",2),FIND("-",SUBSTITUTE(B2," ","-",2),1)-1))-1),Sheet1!$A$1:$F$1,0)-1,FALSE)


Change the blue bit to include everything from the Name column to the end of the table on sheet1 for all rows (excluding the header)
So if you have 50 employees for example and 10 columns of data (with the name of employee being in the second column)
It would be Sheet1!$B$2:$J$51 just as an example. Dont forget the $ symbols

Change the red bit to include all headers to the table on Sheet 1
with the above example (10 columns of data on sheet 1) this section will become
Sheet1!$A$1:$J$1 Don't forget the $ symbols


This is assuming that your actual data on Sheet 2 in column A would be in the format exactly as you have provided

lastname, firstname category (seperated by spaces)


Once you make the adjustments, copy down.

 

[tygrrboi]

=IFERROR(VLOOKUP(LEFT(SUBSTITUTE(B2," ","-",2),FIND("-",SUBSTITUTE(B2," ","-",2),1)-1),Sheet1!$B$2:$F$4,MATCH(RIGHT(B2,LEN(B2)-LEN(LEFT(SUBSTITUTE(B2," ","-",2),FIND("-",SUBSTITUTE(B2," ","-",2),1)-1))-1),Sheet1!$A$1:$F$1,0)-1,FALSE),"")

Would change the #values to blank cells...

 

[Ankitsahgal]

Thank you tygrrboi. This seems to work absolutely perfect.

However, my sheet is set up in the below format

[TABLE="width: 738"]
<tbody>[TR]
[TD]GL Code
[/TD]
[TD]Description[/TD]
[TD]Amount[/TD]
[TD]Employee Number[/TD]
[TD]Category
[/TD]
[TD]Employee Name[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD]Smith, John Basic Salary[/TD]
[TD="align: right"]£200[/TD]
[TD="align: right"]3[/TD]
[TD]Basic Salary[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[TD]Smith, John Overtime[/TD]
[TD="align: right"]£10[/TD]
[TD][/TD]
[TD]Overtime[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD]Smith, John Lieu[/TD]
[TD="align: right"]£0[/TD]
[TD][/TD]
[TD]Lieu Time[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD="align: right"]3500[/TD]
[TD]Smith, John Bonus[/TD]
[TD="align: right"]£0[/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD]Porter, Helen Basic Salary[/TD]
[TD="align: right"]£210[/TD]
[TD="align: right"]64[/TD]
[TD]Basic Salary[/TD]
[TD]Porter, Helen[/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[TD]Porter, Helen Overtime[/TD]
[TD="align: right"]£0[/TD]
[TD][/TD]
[TD]Overtime[/TD]
[TD]Porter, Helen
[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD]Porter, Helen Lieu[/TD]
[TD="align: right"]£35[/TD]
[TD][/TD]
[TD]Lieu Time[/TD]
[TD]Porter, Helen
[/TD]
[/TR]
[TR]
[TD="align: right"]3500[/TD]
[TD]Porter, Helen Bonus[/TD]
[TD="align: right"]£0[/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD]Porter, Helen[/TD]
[/TR]
</tbody>[/TABLE]

I have everything being populated automatically when the employee number is correct. However, I am unable to automate it i.e, I need to manually enter the next employee number as, if I reference first employee cell and add one, it gives me the value 4, as opposed to 64 ( which is the next employee number)

Thank you for your response.

Ankit

 

[tygrrboi]

Do you want the employee number to be added automatically when you input the Description?

I am confused by what exactly you want to accomplish.

Do you just want to have an equation in Sheet 2 Employee Numbers column that repeats every four cells? copying the table from Sheet1?
if so:

in Sheet2!$D$2 =INDIRECT("Sheet1!" & ADDRESS(1+(CEILING(ROW()-1,4)/4), 1,4))

 

[Ankitsahgal]

Thank you Tygrrboi. That is exactly what I needed.

All my other fields in sheet 2 (GL code, amount, description, etc) are being populated based on employee number.

How would I replicate the formula if the Employee name repeated every 52 cells ?

 

[tygrrboi]

You mean to say apart from Salary, Overtime, Bonus, Lieu... There are a total of 52 columns after Employee Name in sheet1?

in Sheet2!$D$2 =INDIRECT("Sheet1!" & ADDRESS(1+(CEILING(ROW()-1,4)/4), 1,4))

replace the red numbers with the number of columns you have.

Dont change the blue 4. This designates the address as not absolute. (it makes it A1 instead of $A$1)