How to Move Data in Excel

smogsy

smogsy

smogsy

Soldato
Joined
9 Dec 2006
Posts
9,289
Location
@ManCave
Firstly, i did know how to do this at one point but i haven't touched excel in years & wants to start learning again.

Heres what i want to do not sure how to do it
In Sheet1 there will be many field one being "Model" (which is a column)

Now in Sheet 2 there are grouped section for each "model" (which are in row format) with the same fields. this sheet is an overview.

what i want to is:
Move the data from sheet 1 to sheet 2 depending on there model (the entire row)

for example Sheet 1 one model could be be: LE453BMX - this would automatically (or by a button update) copy itself to Sheet 2 in the correct section.

how would i go about doing this? :D
 
"=Sheet1!A3" If you place that formula on the second sheet, it would refer to the value that is in Cell "A3" on "Sheet1". Just change it according to where the data is that you want to appear. This is known also as relative referencing I believe. :)
 
Ok, i had to change the data/field a little as its for work.

See Link below for the execel file
www.smogsy.co.uk/work.xls

just the clarify:
i want to somehow search Sheet1 B Column for certain Model then copy the contents of that row to The correct area on Sheet2 & repeat for each section :)
 
Last edited:
I've done something almost identical to this at work this week using VBA. I'll try and get around to this a little later today if nobody else has posted a working response before then.
 
Done.

http://hotfile.com/dl/62038393/ca0ee4c/work.xls.html

When you open the file, it will be in the empty Work in Progress sheet - don't panic. Click on the Log Sheet, click back to the Work in Progress sheet and watch what it does. There might be a delay - once the colouring has been done, it has finished. We'll come back to this delay in a moment.

The information it has produced is based on the list of models in the Model List sheet which you can add to. It is being instructed to look for the data in the Log Sheet which matches the model numbers entered in the Model List sheet. If you right-click on the Work in Progress tab name and go to View Code, you'll see what I'm getting it to do every single time you switch to the Work in Progress sheet. I've annotated the coding so you can hopefully follow what is happening.

If the delay is annoying you, reduce the number of rows the For/Next instructions are looking at and increase them only when necessary. I've set them to 500 for the moment which is far in excess of the data held in the spreadsheet.

Any problems/questions, hopefully I'll check this thread later!
 
Last edited:
A macro sounds a bit like overkill. I expect you're best off using a combination of index and match functions. Also you'll need to use offset to adjust the range you're looking up from so that you can pull the second, third, etc. instance of each model.
 
Psiko said:
A macro sounds a bit like overkill. I expect you're best off using a combination of index and match functions. Also you'll need to use offset to adjust the range you're looking up from so that you can pull the second, third, etc. instance of each model.
Click to expand...

I disagree in this instance.

Given that he will have a large amount of data which would be unsorted at the time of its entry and deletion is a possibility, an INDEX/MATCH/OFFSET formula which, if I recall correctly from one I use in a spreadsheet at work, has an element of manual specification to it wouldn't necessarily behave correctly when being asked to list all 35 instances of a model number. I could be slightly wrong there although I'm sure it ultimately wouldn't work.

The macro functions as though it were a VLOOKUP working within two For Next clusters which allow it to function better than a standard VLOOKUP formula which would only pick up the first entry. I just think its altogether faster and yet not that complicated.
 
thanks for the big help skeeny, it worked at work on my machine but not on my bosses. were looking into to that atm seems hes missing a DLL!.

thank you again for the time you put into this, once i get time i will be modfying it to work exactly how we want it and with the correct data not fake data like what was in the execel sheet :)
 
You must log in or register to reply here.
Back
Top Bottom