We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copying rows from one sheet to another sheet using excel Formula

Hi,

I am having following Requirement.On sheet 1, I am having data as follows :

Product Country Price
ABC Ind 10
CDE SLN 20
EFG BAN 30
ABC SLN 40

I want to show all the records on sheet 2 for Product ABC using any EXCEL FORMULA.

I tried VLOOKUP and MATCH , but I am not getting the solution.

Is there any Function with which we can accomplish the above mentioned.

If yes,Please provide sample example.

Thanks,

Hi Jack,


Thank you for contacting Aspose support.

You can use the Excel’s VLOOKUP formula to pick individual cell values and show them on another worksheet as a reference. Please check the following code snippet that uses the same approach to show all values from Sheet1 on Sheet2. You can surely amend the code to achieve your custom requirement.

C#

Workbook workbook = new Workbook(myDir + “Book2.xlsx”);
Worksheet worksheet = workbook.Worksheets[1];
for (int i = 0; i <= 3; i++)
{
for (int j = 0; j <= 2; j++)
{
worksheet.Cells[i, j].Formula = “=VLOOKUP(Sheet1!A” + (i + 1) + “,data,” + (j + 1) + “,FALSE)”;
}
}
workbook.CalculateFormula();
workbook.Save(myDir + “VLOOKUP.xlsx”, SaveFormat.Xlsx);

You can also use the Aspose.Cells' Find functionality to seek for a particular text in a given spreadsheet, and copy the entire row to another worksheet. Please check the following code snippet and attached resultant spreadsheet for your reference.

C#
Workbook workbook = new Workbook(myDir + "Book2.xlsx"); Worksheet sourceWorksheet = workbook.Worksheets[0]; Cells sourceCells = sourceWorksheet.Cells;
FindOptions opts = new FindOptions(); opts.LookInType = Aspose.Cells.LookInType.Values; opts.LookAtType = Aspose.Cells.LookAtType.EntireContent;
Cell cell = sourceCells.Find("ABC", null, opts); System.Console.WriteLine("Name of the cell containing required text: " + cell.Name);
int row = 0; Worksheet destinationWorksheet = workbook.Worksheets[1]; Cells destinationCells = destinationWorksheet.Cells; destinationCells.CopyRow(sourceCells, cell.Row, row++ );

workbook.Save(myDir + "Find.xlsx", SaveFormat.Xlsx);

Please feel free to write back in case you need our further assistance with Aspose APIs.

Hi Babar,

Thanks for your response.

I have one question. The above code, we will use while creating the workbook. I am in need of functionality after we create the workbook.

Suppose, In my workbook, on One sheet I am having data and one Combo box/Drop down.

If I select any product, then corresponding row has to show on other sheet.

In other sense, i am looking for event handling kind of stuff in excel through Aspose.

Is this possible using some Excel formulas.

If yes, please provide sample example/sheet.

Thanks,

Hi Jack,


I think your requirement can be achieved using macros, however, Aspose.Cells APIs do not support creation, manipulation or triggering of macros in a spreadsheet. The API can only preserve or remove them while processing a spreadsheet containing macros.

Hi,

While surfing the net, i got the following formula for returning multiple values.

=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))

On following link :

http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

Could you please suggest any other related formula/suggestion to accomplish my requirement, as this formula looks complex and i need to copy in every cell.

Sorry for any inconvenience caused my post.

Thanks

Hi Jack,


Thank you for writing back,

Unfortunately, I couldn’t think of a simple formula that could enable you to copy an entire row from one worksheet to another, this is because; more the cells are involved more the formula will become complex. Anyway, you should be looking for a VBA based solution for your scenario. I think if you search the internet for VBA, you will find pretty simple modules to achieve your requirements.

Hi Jack,

Thanks for using Aspose.Cells.

For your requirement, here is a one solution you may try.

Java


Workbook wb = new Workbook(“VLOOKUP.xlsx”);

Cells cellsData = wb.Worksheets[0].Cells;

int maxDataCol = cellsData.MaxDataColumn + 1;

int maxDataRow = cellsData.MaxDataRow + 1;

Cell cell = cellsData[0, maxDataCol];

cell.SetSharedFormula("=ROW()", maxDataRow, 1);

Worksheet sheetView = wb.Worksheets[1];

Cells cellsView = sheetView.Cells;

cellsView.Columns[0].IsHidden = true;

cell = cellsView[2, 0];

maxDataCol++;

cell.SetSharedFormula("=VLOOKUP(INDIRECT(“Sheet1!A”&$A$1),INDIRECT(“Sheet1!A”&(A2+1)&":"&""

+ CellsHelper.CellIndexToName(maxDataRow, maxDataCol) + “”)," + maxDataCol + “,FALSE)”,

maxDataRow, 1);

cell = cellsView[2, 1];

cell.SetSharedFormula("=IFERROR(OFFSET(INDIRECT(“Sheet1!A”&$A3),0,COLUMN()-2),"")", maxDataRow, maxDataCol - 1);

ShapeCollection shapes = sheetView.Shapes;

Shape s = shapes.AddShape(MsoDrawingType.ComboBox, 0, 0, 1, 0, 30, 100);

ComboBox cb = (ComboBox)s;

cb.InputRange= “Sheet1!A1:A” + maxDataRow;

cb.LinkedCell = “A1”;

wb.Save(“res.xlsx”);