#1




I want to use formula to auto update cells with information
Basically, I have spent half the day trying to work out if this is possible, to create a formula to take all the tidy up the titles under one location, I have tried a variety of Match and Index functions, Tried Lookups, only just started getting to grips with the CTRL+SHIFT+ENTER array formula but seem to only get the same result in every cell, not sure if that is right.
anyway I have lost all hope right now, hence why I am on here hoping for some help. please find below a link of what I would like, https://www.dropbox.com/s/e7feu2fvjz...Help.xlsx?dl=0 Thanks in advance for any help given. Kind Regards Steve 
#2




Hi
external links being potentially unsfae, plesae post a sample sheet on the forum  Thx 
#3




I was beginning to think that my post hadn't been posted..
Apologies for posting external link, I have created an image of it instead. I hope that this helps give the example ? am still struggling to work out how I can do this Maybe I should add I have been working on this from other sources of data and the row at the top is generated from an Index function itself so that if any of the source data from other locations change so will the data here, which I then want to automatically be sorted into the rows. from this I am going to be putting into another sheet so that the statistics and data can be tidied up. this is the final part of the hurdle that I just cannot figure out myself. Thanks again for any help. Last edited by Steve81uk; 01082015 at 03:00 AM. Reason: wanted to add a bit more information about why I am requesting for the help. 
#4




Hi
I'm sorry I didn't explain better. Please post a sheet. Pics are nice to look at but impossible to work with. Could you also explain better what you need, I really don't understand. As for the delays in answering, well you know, this is a free forum, so members answer when they have some spare time. Sometimes it takes a couple of minutes, sometimes days... 
#5




Oh sorry I did not mean to come off as impatient for a response, I know there could be a wait, am fine with that I feel I need a break from this at the moment to be honest. makes the head hurt.. I just meant that I did not even see my post posted.
okay, am not good at explaining I guess. I'l try with the use of pictures, 1. I have a huge list with lots of information, this I have managed to sort into alphabetical order by use of a variety of function statements. 2. I managed to use the INDEX function to transpose the data as I could not work out any other way. 3. My main aim is to have the columns beside each Location to automatically show any data that is in the same location (to start from column B, I worked out one way but it gave the data from the Column starting with that location) Maybe I am going the wrong way completely to get to the result I want, if you can do it without even transposing the data that will be even better. (I need it in this format to be able to progress to my next step of what I need to do with this data, But no worries I should be running after this stage, and should hopefully not need any more help) This is possibly the clearest I can try and make it, I apologise if this still needs better explaining. Thanks again. Steve Last edited by Steve81uk; 01082015 at 05:33 AM. Reason: made clearer still. 
#6




Sorry to insist, could you post a sheet please?
Click the " Go advanced " button under a quick reply message and click on "manage attachments". Follow the wizard to attach your sheets 
#7




Here you go, I have had to heavily edit parts of it as it is for my work, and don't want to give out any information. it has formula to collect data from another document that I certainly cannot attach.
Steve 
#8




Thanks for the sheet, and what it is a good idea to strip it of any confidential information.
OTH, I still don't understand what you want to do ( probably my Engish letting me down or my age..). You want to populate sheet "RowLabels", but what would be an example of outcome for, say,C6 or G15 ? ( picked that at random) 
#9




Sorry
Lets see if the attached helps, It is difficult for me to exactly explain without being able to show it. I have attached and added a new tab at the bottom with how I would like it to look. Thanks very much for your help already , hopefully together we can do this:) Steve 
#10




Is there anything different from your previous attachment?

#11




Dunno how I managed to do that! trying again. this really is not going my way is it..
New attachment. Its not how I edited it at home yesterday, but it should be (hopefully) easy to understand. Steve 
#12




Using your last sheet insert the following array formula in B5
Code:
=IFERROR(INDEX($A$2:$LZ$2,SMALL(IF($A$1:$LZ$1=$A5,COLUMN($A$1:$LZ$1)),COLUMN(A:A)));"") Next drag down and right as far as required. It is possible that the calculations may take a little more time than usual. 
#13




Been trying to do exactly what you posted. However I keep getting a error message saying "The formula you typed is incorrect", I have tried doing the CTRL+SHIFT+ENTER before and after putting in the formula, but no luck. I do have to admit I had only just three days ago heard of the Array formulas so I do not have much understanding of this.
Thank you for your time and patience, I am sure that we will get there eventually Steve 
#14




My error I left a semi colon in the formula
Try =IFERROR(INDEX($A$2:$LZ$2,SMALL(IF($A$1:$LZ$1=$A5, COLUMN($A$1:$LZ$1)),COLUMN(A:A))),"") You must commit with the insertion point in the formula bar. Curly braces will appear. You cannot add them manually 
#15




Works perfect
Thanks very very very much. Steve 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Update cells on demand  required_username  Excel  3  11242014 03:56 AM 
matching data from one work sheet to another, then auto filling missing cell information  timomaha  Excel  1  09122014 07:51 AM 
How to merge multiple records into one and update the cells  mag  Excel  1  10302012 01:11 AM 
Auto Update the date.  Nirik  Excel  16  12162010 04:23 AM 
Project AutoUpdate  hBsys  Project  0  04152010 06:46 AM 