Lookin up reports and copping them acroos

Trikeman · Mar 8, 2018

Hi Guys,
Just wondered if anyone could point me in the right direction, I have a long list of monthly reports on a sheet called “MONTHLY REPORTS DATA” the most recent report at the top and then the second to most recent and so on, all the reports are 15 rows high and 8 columns wide, I have another sheet called (100) and I have part of it all format ted and the width and columns are the same size and formatting as the reports are, I need a quick and easy way of choosing which report I need to see and have it popup on sheet (100) where I have matched the formatting.

Each report has its own ref number, so I, have a list of all the reports ref numbers on sheet (100) and I have gone through the steps of using the find and replace to find the required ref number and then copy and paste it across, and this works well but takes to long, I was wondering about using the “find” in vba and telling vba what to select and copy and paste it across, but I would need a way of choosing the ref number, maybe through an input box, but I don’t know if you can give the find command a value from an input box.

I would be very happy to here if anyone knows an easier way of doing this.

Thanks everyone.
Trikeman

Special-K99 · Mar 8, 2018

"Each report is 15 rows high and 8 columns wide"

Three questions

1. On what row number does report number 1 start ?
2. On what row number does report number 2 start ?
3. Report number 1: Where does column 1 of the report begin ? Is it column A ?

Once you've answered these three questions it should be easy to work out where the other reports are and provide a simple INDEX() formula.

Remember this assumes as youve said that ALL reports are 15 rows high and 8 columns wide and any future reports will be the same size.

Trikeman · Mar 8, 2018

Special-K99 said:
"Each report is 15 rows high and 8 columns wide"

Three questions

1. On what row number does report number 1 start ?
2. On what row number does report number 2 start ?
3. Report number 1: Where does column 1 of the report begin ? Is it column A ?

Once you've answered these three questions it should be easy to work out where the other reports are and provide a simple INDEX() formula.

Remember this assumes as youve said that ALL reports are 15 rows high and 8 columns wide and any future reports will be the same size.

The top report starts at row 1 column A and finishes at row 15 column H, there are then 5 blank rows before the second report started at row 21 and goes down to H35, and so on. At the moment I have a macro which makes the report from data I have entered into another sheet and it inserts 20 new rows at the top of the sheet, when it makes each report so the most recent report is always the top one, although if this was a big problem with each report moving down 20 rows each month there is no good reason why I could not change it so the most recent is always added to the bottom, in fact please could you tell me which would be the normal way of doing it, put the latest report at the top or bottom? Yes the reports will stay the same size.
Thank you for your help.

Special-K99 · Mar 9, 2018

You'll have to modify this to fit your "100" spreadsheet and the drawback is it uses INDIRECT() so any new reports don't mess up the formula.
Try this see what you think.

Insert a new sheet, let's call it Sheet1

in Sheet1!A1 type the number of the report you want to look at, 1, 2, 3, 4, 5, etc

in Sheet2!A3
=INDEX(INDIRECT("Sheet1!A1:H20000"),$A$1*20-20+ROW()-2,COLUMN())
copy across and down as far as H17

Now when you change the value in Sheet1!A1 to 2 it will change to the second report.

It will produce 0s instead of blanks, not sure if thats important.
If it is you can change the cells to custom format to display blanks instead of zeroes (cant recall what the format is)

Trikeman · Mar 10, 2018

Thank you for that code, unfortunately I can’t get it to work, if I leave the code as it is, all I get is the #value error. If I take the second twenty out, =INDEX(INDIRECT("Sheet1!A1:H20000"),$A$1*20-20+ROW()-2,COLUMN()) to =INDEX(INDIRECT("Sheet1!A1:H20000"),$A$1*20-0+ROW()-2,COLUMN()) I get the top form, however this takes no notice of what’s in the cell A1, I can have it blank or I can put any number I like in the cell for A1 sheet1, and all I get is the first form.

The strange thing is, I created two new work sheets, called sheet1 and sheet2 and copied some of the forms into sheet1 and put the code into a3 sheet2 as per the instructions, but after changing that 20 to a 0 as above, I got only form 1 as I said but then when I went back to sheet1 and cleared all the forms out the information from sheet 1 stays on sheet2, really don’t understand what’s going on there.

My original idea was when the macro makes each form, once the new form is made, the macro would select it then name it and copy the name to a dynamic name list which would feed a drop down menu and some how have it set up so the name in the menu pops the right form up but I can’t find away to name a range in vba, even if I could I am not sure how selecting a name in a drop down menu would make the form pop up, having said all that if there is away to get this formula working I would be very happy with that. Any ideas please.

Thank you for reading.

Special-K99 · Mar 12, 2018

So the data is 15 rows by 8 columns but the area of the actual report is 20 rows by 8 columns
Data starts in A1

Based on that

in your proper file
create 2 blank worksheets Sheet1 and Sheet2

in Sheet2!A1 put a1
in Sheet2!H1 put h1
in Sheet2!A20 put a20
in Sheet2!H20 put h20
in Sheet2!A21 put a21
in Sheet2!H21 put h21
in Sheet2!A40 put a40
in Sheet2!H40 put h40

the above defines 2 report areas starting at Sheet2!A1:H20 and Sheet2!A21:H40

in Sheet1!A3
=INDEX(INDIRECT("Sheet2!A1:H20000"),$A$1*20-20+ROW()-2,COLUMN())
copy down as far as H22

in Sheet1!A1 put 1
You should see the extent of report 1, ie a1, h1, a20, h20 listed.

Now change Sheet1!A1 to 2
The report changes to a21, h21, a40, h40
which is the second report.

Now change the formulas in Sheet1 to refer to your report sheet rather than Sheet2

Trikeman · Mar 12, 2018

Thank you, I have just come in and seen your email, will be trying that this after tea and will will report back. A big thank you to you for helping me with this.

Trikeman · Mar 13, 2018

Just to let you know, that formula is in and working perfectly, I want to say a really big thank you for writtting this formula, I could of sat at my computer for ever and a day and not got anywhere near to this formula. So yes thank you so much.
Trikeman

Special-K99 · Mar 13, 2018

You're welcome

Lookin up reports and copping them acroos

Trikeman

New Member

Excel Facts

Special-K99

Well-known Member

Trikeman

New Member

Special-K99

Well-known Member

Trikeman

New Member

Special-K99

Well-known Member

Trikeman

New Member

Trikeman

New Member

Special-K99

Well-known Member

Similar threads

Forum statistics

Share this page

Lookin up reports and copping them acroos

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock