Excel Fundis Needed

Progenix Oj101

Progenix Oj101

Dealer
Joined
Jun 4, 2010
Messages
6,660
Reaction score
4,208
Location
Kempton Park
Let me start by showing the approximate layout of the spreadsheet:

spreadsheet.JPG

Category is not important for this - it is merely for ease of navigating that sheet when adding/editing data.

What I need to do is in another sheet have the following:

spreadsheet2.JPG

Blue text is just to explain what's happening, red is all filled in dynamically from the first sheet based on the selections in the dropdown menus. Can anyone help with this? I've Googled, but it's a bit beyond me.
 
Hi, there are many ways to skin a cat. Here is one which uses the non-volatile (good!) index formula, and the fact that each item table is of exactly the same size (a 7x5 table).

For this to work, B4 must say just "1" and not "Item 1" (we can fix that easily later, if this works for you):
In C5 put the following formula
=INDEX(sourcedata, ROW(A1)+($B$4-1)*7,COLUMN(A1))

where sourcedata is the whole data range in the first table for column E to I e.g. 'Sheet1'!$E$2:$I$10000

You can then copy that formula in C5 across the whole table down to G11.
 
themba990 said:
Offset could also work
Click to expand...

The offset function was my initial choice too. However it is volatile, meaning the cell will recalculate with every and all changes in workbook (possibly even changes in other opened workbooks). This can have a negative impact on performance, especially with large data sets.

Devilish said:
Why don't you use a Pivot Table?
Click to expand...

Pivots are a good no-formula option, but can be equally daunting to set up if you don't know it well. Also if the data is basically just being reproduced 1-to-1 (as is the case here), the storage and processing penalty associated with pivot tables may not be worth it. That said, if the OP wants to further summarise the data, a pivot table / chart may be a great choice.

* add own cat skinning gif here *
 
Can the pivot tables be done with the layouts I provided in my first post (merged cells, the results being side by side with a gap between them, etc?
 
[XC] Oj101 said:
Is there a solution that WILL work like post 1? The layout is quite (read: very) important.
Click to expand...
What you could do is repeat the rows like the data table required for the pivot table example.
Then change the font colour of the text to white to make all the text in the cells that are showing white invisible.
Cannot merge cells though as it will not aggregate your information in the pivot correctly.
 
Keep it simple or you will have a massive headache maintaining the data integrity.
Especially if you are not used to Excel formulas and are handing it over to average users.
 
kabusi said:
Keep it simple or you will have a massive headache maintaining the data integrity.
Especially if you are not used to Excel formulas and are handing it over to average users.
Click to expand...
Simple is why I need the merged cells. Duplicated data all over the place is going to be... let’s just say too confusing for the users. Nobody will be touching formulas to once it is setup.

(I’m beginning to think I should just do it in HTML/PHP/SQL, at least I know my way around that well enough... but ideally it really needs to be in Excel laid out as per my first post :()
 
Mobus said:
Hi, there are many ways to skin a cat. Here is one which uses the non-volatile (good!) index formula, and the fact that each item table is of exactly the same size (a 7x5 table).

For this to work, B4 must say just "1" and not "Item 1" (we can fix that easily later, if this works for you):
In C5 put the following formula
=INDEX(sourcedata, ROW(A1)+($B$4-1)*7,COLUMN(A1))

where sourcedata is the whole data range in the first table for column E to I e.g. 'Sheet1'!$E$2:$I$10000

You can then copy that formula in C5 across the whole table down to G11.
Click to expand...

Can you break this down for me a bit? Also, "1" will not work - it will be the name of the item. Users won't have a clue what is what if they have to select a value instead of the name of the item :(
 
If you're going to work a lot with this in the future, redesign the whole thing and normalize it, save yourself a lot of effort going further.
 
Mobus said:
Hi, there are many ways to skin a cat. Here is one which uses the non-volatile (good!) index formula, and the fact that each item table is of exactly the same size (a 7x5 table).

For this to work, B4 must say just "1" and not "Item 1" (we can fix that easily later, if this works for you):
In C5 put the following formula
=INDEX(sourcedata, ROW(A1)+($B$4-1)*7,COLUMN(A1))

where sourcedata is the whole data range in the first table for column E to I e.g. 'Sheet1'!$E$2:$I$10000

You can then copy that formula in C5 across the whole table down to G11.
Click to expand...

OK I think I got this working. B4 is not the selector, though - I need dropdown menus for that, B4 must just specify what is currently being viewed.
 
Then just click on each total you want to add up for sum 1 and drag your formula to the right.
 
[XC] Oj101 said:
Can the pivot tables be done with the layouts I provided in my first post (merged cells, the results being side by side with a gap between them, etc?
Click to expand...
Hide the database layout columns and insert columns reflecting your layout.

To keep the gap between the pivots just add another pivot next to the first. :)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X