Cell Reference from another worksheet

[Ankitsahgal]

Hi All,

I have in sheet1 , the following data

Employee Number	Name	Basic Salary	Overtime	Lieu	Bonus
3	Smith, John	£200	£10	£0	£0
64	Porter, Helen	£210	£0	£35	£0
38	Rayne, Alex	£300	£0	£0	£50

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

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

GL Code	Description	Amount
2000	Smith, John Basic Salary	£200
3000	Smith, John Overtime	£10
2000	Smith, John Lieu	£0
3500	Smith, John Bonus	£0
2000	Porter, Helen Basic Salary	£210
3000	Porter, Helen Overtime	£0
2000	Porter, Helen Lieu	£35
3500	Porter, Helen Bonus	£0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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

GL Code	Description	Amount	Employee Number	Category	Employee Name
2000	Smith, John Basic Salary	£200	3	Basic Salary	Smith, John
3000	Smith, John Overtime	£10		Overtime	Smith, John
2000	Smith, John Lieu	£0		Lieu Time	Smith, John
3500	Smith, John Bonus	£0		Bonus	Smith, John
2000	Porter, Helen Basic Salary	£210	64	Basic Salary	Porter, Helen
3000	Porter, Helen Overtime	£0		Overtime	Porter, Helen
2000	Porter, Helen Lieu	£35		Lieu Time	Porter, Helen
3500	Porter, Helen Bonus	£0		Bonus	Porter, Helen

<tbody>
</tbody>

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)