Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2015, 08:07 AM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Unhappy 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
Reply With Quote
  #2  
Old 01-08-2015, 02:26 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Hi
external links being potentially unsfae, plesae post a sample sheet on the forum - Thx
Reply With Quote
  #3  
Old 01-08-2015, 02:42 AM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Default

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; 01-08-2015 at 03:00 AM. Reason: wanted to add a bit more information about why I am requesting for the help.
Reply With Quote
  #4  
Old 01-08-2015, 05:03 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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...
Reply With Quote
  #5  
Old 01-08-2015, 05:31 AM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Default

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; 01-08-2015 at 05:33 AM. Reason: made clearer still.
Reply With Quote
  #6  
Old 01-08-2015, 06:03 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #7  
Old 01-08-2015, 06:17 AM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Default

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
Attached Files
File Type: xlsx Help.xlsx (226.8 KB, 3 views)
Reply With Quote
  #8  
Old 01-08-2015, 12:59 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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)
Reply With Quote
  #9  
Old 01-08-2015, 02:01 PM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Default

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
Attached Files
File Type: xlsx Help.xlsx (226.8 KB, 4 views)
Reply With Quote
  #10  
Old 01-09-2015, 02:01 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Is there anything different from your previous attachment?
Reply With Quote
  #11  
Old 01-09-2015, 02:13 AM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Default

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
Attached Files
File Type: xlsx Help.xlsx (199.5 KB, 7 views)
Reply With Quote
  #12  
Old 01-09-2015, 09:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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)));"")
Then commit this formula with Ctrl+Shift+Enter

Next drag down and right as far as required.

It is possible that the calculations may take a little more time than usual.
Reply With Quote
  #13  
Old 01-09-2015, 11:22 AM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 01-09-2015, 12:51 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #15  
Old 01-09-2015, 12:56 PM
Steve81uk Steve81uk is offline I want to use formula to auto update cells with information Windows 7 64bit I want to use formula to auto update cells with information Office 2013
Novice
I want to use formula to auto update cells with information
 
Join Date: Jan 2015
Posts: 16
Steve81uk is on a distinguished road
Default

Works perfect

Thanks very very very much.

Steve
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update cells on demand required_username Excel 3 11-24-2014 03:56 AM
I want to use formula to auto update cells with information matching data from one work sheet to another, then auto filling missing cell information timomaha Excel 1 09-12-2014 07:51 AM
I want to use formula to auto update cells with information How to merge multiple records into one and update the cells mag Excel 1 10-30-2012 01:11 AM
Auto Update the date. Nirik Excel 16 12-16-2010 04:23 AM
Project Auto-Update hB-sys Project 0 04-15-2010 06:46 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft