Free Support Forum -

Use IF formula to place value in different cell in list of data


sample data:(asset class, security type, mv)

Equity, ETF, 10000

Equity, Common stock, 2500000

Fixed Income, CB, 125000

Fixed Income, T-Bill, 20000

I have a list of data I'm displaying in Excel with Asset class and security type. There can be multiple security types for each asset class. The asset class header row needs to be displayed in once color and the security type rows need to be displayed in another color. I accomplished that through Conditional formatting.

I only want to show the asset class once per group. so I tried changing the font of the asset class to match the font of the cell on the security type rows but when you try to print it (and in print preview) it prints the asset class for each security type row in column A. So I moved that data to column E and made it white to match the cell so it wont be visible and then in column A use a formula to check if the value was a header and if it is then place asset class value in column A.

but that only works for the first row.

here's my IF statement in column A. =IF(D16=1,E16,"")

please see attached spreadsheet for example.



Well, if one has his his own way and want to avail his custom needs, then Aspose.Cells’ smart marker feature might not work fully for him all the time as the feature is for general purpose. So, you have to write your own code to accomplish your custom tasks once the markers are processed, there would be no automatic way to do that. Anyways, we still recommend you to check the following document, especially the sub-topic “Grouping Data” on that page, so you may check the description and example codes:
E.g. you may try group:normal or group:merge parameters while grouping data as mentioned on the page.

For “but that only works for the first row.
here’s my IF statement in column A. =IF(D16=1,E16,”")"
may be you could try using our SharedFormula feature if it may help a bit. This feature would paste corresponding formulas (adding increment to cells’ rows/columns for the original formula) in a range of cells:

Thank you.