-1

I am working on a spreadsheet in Microsoft excel 2010. In the spreadsheet I have 3 worksheets. These are known as:

  • 1st
  • 2nd
  • Cases

Now in the 1st and 2nd Worksheets, there is a table at the bottom which contains case details. In the Cases worksheet there is a case table as well but it's empty. What I want to do is the data from both 1st and 2nd Worksheet be automatically inserted in the Cases worksheet table.

How can this be achieved? I have tried consolidation by category by clicking on "Consolidate, selecting the relvant lables and data from the 1st and 2nd worksheet, and added them and clicked ok but it gives me message saying no data was consolidated.

Here is a link to the spreadsheet which can be downloaded:

Spreadsheet

Can you give me any suggestions on this or can somebody try and apply my spreadsheet onto an excel document and see if they can get it working?

P.S I know the 2nd Worksheet has blank data in its Case summary table, but this table can consist some data in the future.

Thank You

2
  • You should upload your Excel file somewhere else or you will get maaany access requests. Or you have to set the permission to public?
    – nixda
    Nov 5, 2013 at 18:56
  • @nixda Thanks for the link and comment, I put the spreadsheet on that file uploaded you have showed me.
    – user21255
    Nov 5, 2013 at 19:01

1 Answer 1

1

Insert this formula into the cell B4 of the sheet "Cases":

=IF(NOT(ISBLANK('1st'!B25)),'1st'!B25,IF(NOT(ISBLANK(INDIRECT("'2nd'!R" & (ROW($B4)-(COUNTA('1st'!$B:$B)-COUNTA('1st'!$B$1:$B$24))-4+25) & "C" & COLUMN(B4),FALSE))),INDIRECT("'2nd'!R" & (ROW($B4)-(COUNTA('1st'!$B:$B)-COUNTA('1st'!$B$1:$B$24))-4+25) & "C" & COLUMN(B4),FALSE),""))

Copy the formula to the other cells in the worksheet; the relative addresses will adjust automatically.


The formula works like this:

  1. Check if there is content in 1st. If yes, copy it.
  2. If no, find out how many entries there are in 1st in total. (This is done by using the COUNTA function on the whole B column in 1st and subtracting the number of non-empty cells above the actual case data.) Use this information together with the current cells's number to find out the location of the cell that has to be copied from 2nd.
  3. Create the address of the cell and use the ISBLANK function on the INDIRECT function with that address to check if the cell is empty.
  4. If it is not, use the INDIRECT function again to display it. If it is empty, just display an empty string.

While this works, it's somewhat of a mess and - depending on the number of cases - may cause performance troubles because the use of lots of INDIRECT functions is pretty slow.

I'd recommend to use another tool for a task like yours - for example Microsoft Access or some other database tool.

8
  • Hi, yeah this is my cousin's spreadsheet who asked me to help him on this. Tbh I was thinking when looking at it that it would be easier if in database software (especially when I am more suited in databases). I will try your theory and hopefully it works. I will probably try to get the spreadsheet working and send it to him and then build an Access database version for better use. I will update you on what is happening when trying your solution. Thanks
    – user21255
    Nov 5, 2013 at 20:00
  • Hi, I am just having one little issue, it is not able to display anything under the "Case Summary" column when I copy the formula in B4 and then drag it to all rows and columns. Do you know why this is? Also what I find strange is when I do drag the information across, the cells under "Open/Closed" and "Case Number" suddenly isn't merged as it should do, it goes back to being two separate cells, I have to manually merge them again. Do I need to include something like this in the formula?
    – user21255
    Nov 5, 2013 at 20:13
  • You should copy the formula to each column in the first row manually (i.e. select the first column's cell, press Ctrl+C, move to the next column's cell, press Ctrl+V, move to the last column's cell, press Ctrl+V). Drag the whole row to all rows and columns then.
    – Hauke P.
    Nov 5, 2013 at 21:09
  • Hi, I actually tried this earlier , but it states that it can't paste it. It states cannot change part of a merged cell.
    – user21255
    Nov 5, 2013 at 21:13
  • Ah right. You'll have to manually adjust the formula then. Simply replace all the column references that do not start with a $ (e.g. B25 or B4 but not $B4) with the character of the right column and put each resulting formula into the three cells of the first row.
    – Hauke P.
    Nov 5, 2013 at 21:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .